Page 1 of 1

select from tables

Posted: Sun Sep 11, 2011 11:19 am
by YoussefSiblini
Hi I am trying to select all from multiple tables and I am using this line:

Code: Select all

mysql_query("SELECT * FROM antiques,art WHERE email ='$Email'"); 
But it is not working, If I use:

Code: Select all

mysql_query("SELECT * FROM antiques WHERE email ='$Email'"); 
It work but selecting only one table.

Please can some one tell me how to select 2 tables?


Youssef

Re: select from tables

Posted: Sun Sep 11, 2011 11:51 am
by ok
I guess that both tables has an email column, so you need to specify to which one you are referring.

However, it is not advised to use SELECT query on more than one table, instead use UNION or JOIN.

To further help you, please post the table structure and the error message you are getting.

Re: select from tables

Posted: Sun Sep 11, 2011 12:11 pm
by YoussefSiblini
Hi,
Thank you for you reply,
Here is the table structure:
(id firstname lastname producttittle productdescription shippingfirstname shippinglastname address1 address2 city country postalcode phonenumber email Category IpAddress dateadded)
This structure is the same for both tables.
Here is the error message:
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Program Files\EasyPHP-5.3.6.0
I want to output both tables to the user, if there are a better way I will go with it.

Youssef

Re: select from tables

Posted: Sun Sep 11, 2011 12:23 pm
by ok
So try the following SQL query:

Code: Select all

SELECT *
FROM antiques
WHERE antiques.email = '$Email'
UNION ALL
SELECT *
FROM art
WHERE art.email = '$Email'

Re: select from tables

Posted: Sun Sep 11, 2011 12:42 pm
by YoussefSiblini
I tried this:

Code: Select all

$sql_added_Swaps = mysql_query( "SELECT *
                   FROM antiques
                   WHERE antiques.email = '$Email'
                   UNION ALL
                   SELECT * 
		   FROM art 
		   WHERE art.email = '$Email'"); 

		   $productCount = mysql_num_rows($sql_added_Swaps);
		       if ($productCount > 0) 
			   {
				    while($row = mysql_fetch_array($sql_added_Swaps))
				    {
				    $id = $row["id"];
			            $producttittle = $row["producttittle"];
			            $productdescription = $row["productdescription"];

			             $firstname = $row["firstname"];
			             $city = $row["city"];
			             $country = $row["country"];
			             $postalcode = $row["postalcode"];
			             $email = $row["email"];
			             $Category = $row["Category"];// what table is it in
                                     $dateadded = $row["dateadded"];

				    }
			   }
And getting the same error, I posted you the full code so you have a better idea.

Youssef

Re: select from tables

Posted: Sun Sep 11, 2011 12:50 pm
by ok
Add the following code after the mysql_query call:

Code: Select all

if (!$result) {
    die('Invalid query: ' . mysql_error());
}
and post the output here.

Re: select from tables

Posted: Sun Sep 11, 2011 12:56 pm
by YoussefSiblini
I did that and I am getting this error:
Notice: Undefined variable: result in C:\Program Files\EasyPHP-5.3.6.0\www\swap\user_profile.php on line 48
Invalid query: Illegal mix of collations for operation 'UNION'
This line 48: if (!$result) {die('Invalid query: ' . mysql_error());}

Youssef

Re: select from tables

Posted: Sun Sep 11, 2011 1:12 pm
by YoussefSiblini
Oh do you mean

Code: Select all

if (!$sql_added_Swaps ) {
    die('Invalid query: ' . mysql_error());
}
I tried that and I am getting this:
Invalid query: Illegal mix of collations for operation 'UNION'

Re: select from tables

Posted: Sun Sep 11, 2011 1:23 pm
by ok
For UNION to work, you must ensure that both tables are completely identical (even collactions, meaning utf8 etc)

Re: select from tables

Posted: Sun Sep 11, 2011 1:44 pm
by YoussefSiblini
I just checked every thing is exactly identical,
I am not sure can I use UNION inside mysql query the same way as select I mean like the code below?

Code: Select all

$sql_added_Swaps = mysql_query( "SELECT *
                   FROM antiques
                   WHERE antiques.email = '$Email'
                   UNION ALL
                   SELECT * 
                   FROM art 
                   WHERE art.email = '$Email'"); 
if (!$sql_added_Swaps) {die('Invalid query: ' . mysql_error());}

                   $productCount = mysql_num_rows($sql_added_Swaps);
                       if ($productCount > 0) 
                           {
                                    while($row = mysql_fetch_array($sql_added_Swaps))
                                    {
                                    $id = $row["id"];
                                    $producttittle = $row["producttittle"];
                                    $productdescription = $row["productdescription"];

                                     $firstname = $row["firstname"];
                                     $city = $row["city"];
                                     $country = $row["country"];
                                     $postalcode = $row["postalcode"];
                                     $email = $row["email"];
                                     $Category = $row["Category"];// what table is it in
                                     $dateadded = $row["dateadded"];

                                    }
                           }

Re: select from tables

Posted: Sun Sep 11, 2011 1:55 pm
by ok
So try this query:

Code: Select all

SELECT firstname
FROM antiques
WHERE antiques.email = '$Email'
UNION ALL
SELECT firstname
FROM art
WHERE art.email = '$Email'

Re: select from tables

Posted: Sun Sep 11, 2011 1:59 pm
by YoussefSiblini
Not working no idea why, I will not give you any headache any more, I will try to find a different way :) Thank you

Re: select from tables

Posted: Sun Sep 11, 2011 2:46 pm
by YoussefSiblini
I found the error, you were right I am very sorry, I checked again and the Collation were different.


Thank you this code you posted working fine now:

Code: Select all

mysql_query( "SELECT *
                   FROM antiques
                   WHERE antiques.email = '$Email'
                   UNION ALL
                   SELECT * 
                   FROM art 
                   WHERE art.email = '$Email'");
Have a nice evening.

Youssef