|
I am making a custom Sitemap from my database and stuck with following Issue
SQL Query:
SELECT term_id FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"
Query Output
term_id
365
369
370
It returns all term_id from my database through which I gather category slugs from another table to make sitemap URLs. Below is the code and it works fine as you can see in (sitemap output), but unfortunately I am unable to extract next row term_id & it shows always same term_id to me as a result 'Same URL'
My Sitemap Output
<urlset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">
<url>
<loc>
http://domain.com/search-page/?product-cat=mobiles-tablets 365
</loc>
<changefreq>always</changefreq>
<priority>1.0</priority>
</url>
<url>
<loc>
http://domain.com/search-page/?product-cat=mobiles-tablets 365
</loc>
<changefreq>always</changefreq>
<priority>1.0</priority>
</url>
</urlset>
CODE
<?php
header("Content-type: text/xml");
$i=0;
$xml = '<?xml version="1.0" encoding="UTF-8"?>';
$xml.= "\n".'<urlset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
$db = new PDO('mysql:host=xxx;dbname=xxx', 'xx', 'xxx');
$stmt[$i] = $db->query('SELECT count(*) FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"');
$rowcount = $stmt[$i]->fetchColumn();
for ($i=0; $i<2; $i++)
{
$sth[$i] = $db->query('SELECT term_id FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"');
$t_id[$i] = $sth[$i]->fetchColumn();
$stmt[$i] = $db->query('SELECT taxonomy FROM wp_term_taxonomy WHERE term_id = '.$t_id[$i].'');
$t_taxonomy[$i] = $stmt[$i]->fetchColumn();
$stmt[$i] = $db->query('SELECT slug FROM wp_terms WHERE term_id = '.$t_id[$i].'');
$t_slug[$i] = $stmt[$i]->fetchColumn();
}
echo $xml;
for ($i=0; $i<2; $i++)
{
$xml.= "\n\t\t".'<url>'."\n";
$xml.= "\t\t\t".'<loc>'."http://domain.com/search-page/?$t_taxonomy[$i]=$t_slug[$i]"."\t$t_id[$i]\t$i\t$rowcount".'</loc>';
$xml.= "\n\t\t\t".'<changefreq>always</changefreq>';
$xml.= "\n\t\t\t".'<priority>1.0</priority>';
$xml.= "\n\t\t".'</url>'."\n";
}
?>
<?php
$xml.= "\n".'</urlset>';
$handle = fopen('sitemap_custom.xml','w+');
fwrite($handle,$xml);
fclose($handle);
?>
What I Need?
I want to extract next row term_id (ideal if it based on loop[0,1,2]) right now it shows me only result of term_id=365
|
|
|
|
|
It is possible to have a single DataSet having multiple DataTables from multiple databases. If so can you just tell me how ?
|
|
|
|
|
Yes it is.
var ds = new DataSet();
using (var connection = new SqlConnection("..."))
{
var da = new SqlDataAdapter("SELECT ...", connection);
da.SelectCommand.Parameters.AddWithValue(...);
da.Fill(ds, "FirstTable");
}
using (var connection = new OleDbConnection("..."))
{
var da = new OleDbDataAdapter("SELECT ...", connection);
da.SelectCommand.Parameters.AddWithValue(...);
da.Fill(ds, "SecondTable");
}
If one of your queries returns multiple result-sets, you might need to use TableMappings to specify the table names:
DataAdapter DataTable and DataColumn Mappings | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you for your replay .... however, I am looking different databases from the same provider
|
|
|
|
|
So load data from different databases from the same provider then. What's the problem?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Many years ago I experimented with SQL Server returning multiple tables from a single stored procedure. The performance was dramatically slower then return multiple single tables from individual stored procs.
Performance may have changed in recent versions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
MARS be slow, but selecting from a different DB is hardly noticable on SQL Server. Also doesn't need much mucking with datasets, just plain SQL.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all,
I have an SSIS Package, I am using the Oracle Database table as Datasource in my package, when I am trying to Edit and Test the Connection by putting the credentials its giving me the following error.
Test connection failed because of an error in initializing provider. ORA-12541: TNS:no listener
I did set the Oracle_Home to the right directory, still I am getting the problem.
Now I set the TNS_ADMIN also with the network folder still getting the error.
Can anybody help me in resolving this issue, any help is going to be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 13-Jun-17 19:34pm.
|
|
|
|
|
|
I just want to ask, what is your solution on storing encrypted data on database? And also how you do searching on it? For now the subject database server is mysql.
Disk level encryption is not an option. I also research about database level encryption but, MySql TDE is not for me, and also if you migrate to other database server in my understanding you need to re-encrypt all the data.
So my last bet is application level encryption. And I tried to implement it using AES-GCM, but on this I will lose database searching, because encrypting the same data with the same key and id will produce different results. Now I got an idea but, i don't know what would be the risk implementing it. I was thinking blind indexing.
What I am planning to do is to store 2 version of the encrypted data. first the data will be encrypted with aes-gcm, then store another version of it using one-way encryption on this same data will produce the same hash. So the first encryption is reversible but not searchable, while the second one is not reversible but I can do whole word searching.
The obvious drawback of this is speed. But I want also to ask if in security perspective is it feasible? And also how do you do a search query on encrypted database using the partial text of the data?
Thank you
|
|
|
|
|
You do not explain what kind of data it is and what search you want to do...
As for the HASH based search - it will work only if you search for exact data, for instance 'Gilbert Consellado' will produce a constant HASH but the HASH for 'Gilbert' will not be part of it, so you will find that piece of data only if you are looking for 'Gilbert Consellado' exactly as is (include case and space and order)...
If there is a way to index the data, prior encryption, than you can use it (the HASH of the indexes) to search quickly at the DB level...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Hi expert,
Database : MySQL
I am trying to create 2 tables with a one-to-many relationship.
Table 1 - tutor
Table 2 - subject
In my table tutor, I have id as the Primary key and it is auto-incremental.
In my subject, I have id as the Primary key and it is auto-incremental, tutorNRIC as the foreign key and tutorNRIC in my tutor table as the reference key.
For my subject, there will be more than one entry in the column tutor_subject.
For example, a tutor will sign up and then all the subjects that she can teach will be entered into that column tutor_subjet.
I'd like to check if my insert sql is correct :
private final String INSERT_QRY1 = "INSERT INTO hi5project.subject tutor_subject, tutorNRIC VALUES ('?', '?') SET tutorNRIC = hi5project.tutor.tutorNRIC";
Thanks.
|
|
|
|
|
For a start your table design is incorrect, you need a many to many table soa tutor can have mnay students and a student can have many tutors. What happens id=f a tutor leaves, or you have different tutors for different subjects?
LinkID
TutorID
StudentID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Mycroft Holmes,
I am not creating a table to store the students.
So, do I still need a many-to-many relationship between the table : tutor and the table : subject ?
|
|
|
|
|
Sorry my bad - but the same rule applies a tutor may have more than one subject and a subject may have more than one tutor. If these rules apply to your use case then you need a many to many table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK. Tks.
Can I know if the ammended database schema is correct ?
Create table tutor(
id integer, auto_increment;
tutorName varchar(50), not null,
tutorNRIC varchar(50), not null, (Primary Key)
tutorEmail varchar(50), not null,
);
Create table subject(
id integer, auto_increment;
subject varchar950), not null;
subject_id, integer, not null, (Primary Key)
_;
Create table tutor_subject(
tutorNRIC integer,
subject_id integer,
Primary Key(tutorNRIC, subject_id),
Foreign Key(tutorNRIC) references tutor(tutorNRIC),
Foreign Key(subject_id) references subject(subject_id));
|
|
|
|
|
In the Tutor_Subject table I would use the Tutor ID field. The NRIC is a user input field and should not be used as a foreign key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft,
Is it possible to use tutorNRIC as my primary key instead of tutorID ? my tutorID is just an index for me to keep track how many people have signed up.
Another question is that how should I do my insert statement based on many to many relationship ?
|
|
|
|
|
karengsh wrote: Is it possible to use tutorNRIC as my primary key
Yes it is possible just wrong. A user should NEVER be able to edit a primary key.
karengsh wrote: my tutorID is just an index for me to keep track how many people have signed up.
That statement means you need to do some reading/learning on database design. A primary key should be stupid and used for just one purpose, to identify the record, NOTHING else.
Normally you would create the primary records and when you link them (via the UI) you create the link record with the 2 primary keys.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft,
The thing is that in my country NRIC is unique. Nobody holds the same NRIC.
In this case, can I make it my Primary key ? And I'd like tutorID as my index with incremental value?
|
|
|
|
|
You are missing the point - NRIC is EDITABLE by the user therefore it should not be used as a primary key - you can put a unique constraint on it but do not use it as a foreign key in a related table.
karengsh wrote: And I'd like tutorID as my index with incremental value
That is fine but it will not be a count of the records in the table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ok. Tks. Mycroft Holmes.
However, how do I insert into the third table?
Should I use select subj_Id from subject and select tutor_id and then insert into the 3rd table ?
|
|
|
|
|
|
Your question is not very clear.
Are you asking why we use datareader.
The following link might be a good read for you
DataSet vs. DataReader[^]
|
|
|
|
|
I think he just wanted to say hello...
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
