Page 1 of 1

Is this Stupid?

Posted: Thu Mar 20, 2003 1:16 pm
by Igguana
O.K, I'm completely Nerd with PHP, but I try at least to follow the manuals.
Is this statement stupid to search in different tables in one query?

Code: Select all

<?php
$query = "SELECT * FROM $tabla, $tabla2 WHERE ($tabla.Id='$varid' AND $tabla2.Id='$varid')";
?>
When I try it, it gaves me NOTHING (Absolutely) :oops:

Posted: Thu Mar 20, 2003 2:15 pm
by daven

Code: Select all

<?php
$query = "SELECT * FROM ".$tabla.", ".$tabla2." WHERE (".$tabla."Id.='".$varid."' AND ".$tabla2.".Id='".$varid."')"; 
?>
Your logic was fine, but you were using imbedded strings.

Posted: Thu Mar 20, 2003 2:26 pm
by Stoker
perhaps not "stupid" 8O but when quering two tables, and the two tables have columns with the same name, using SELECT * is not exactly "smart", as indicated by your query the fields will be equal anyway so you don't need them both, and if using associative fetching you will only get 1 of them anyway....

ALWAYS (!!!!!!) use explicit column selection, you (almost) NEVER need *

SELECT table1.col1, table1.col2, table3.somecol
FROM table1,table2
WHERE table1.id = $myvar AND table1.id = table2.id

Or in a way that I think is cleaner and easier to read

SELECT table1.col1, table1.col2, table3.somecol
FROM table1
LEFT JON table2 ON table1.id = table2.id
WHERE table1.id = $myvar

Posted: Thu Mar 20, 2003 2:57 pm
by volka
daven wrote:

Code: Select all

<?php
$query = "SELECT * FROM ".$tabla.", ".$tabla2." WHERE (".$tabla."Id.='".$varid."' AND ".$tabla2.".Id='".$varid."')"; 
?>
Your logic was fine, but you were using imbedded strings.
what's wrong with them?

Something wrong!! What can it be?

Posted: Thu Mar 20, 2003 3:17 pm
by Igguana
SELECT * FROM DatosInmueble, FotosInmueble WHERE (DatosInmuebleId.='10602' AND FotosInmueble.Id='10602') :You have an error in your SQL syntax near '='10602' AND FotosInmueble.Id='10602')' at line 1 :(

Posted: Thu Mar 20, 2003 3:56 pm
by daven
Volka:

I have had some funky behavior when using embedded strings like Igguana was. I do not know why, so I tend to stay away from them

Posted: Thu Mar 20, 2003 5:45 pm
by volka
DatosInmuebleId.

Code: Select all

DatosInmueble.Id

Still doesn't work

Posted: Thu Mar 20, 2003 6:29 pm
by Igguana
It keeps giving me an error.

tabla2 and tabla are variables I declare in the context.
varid is a value that comes in the URL from an <a href.
Something to do with that?

What I'm trying to do

Posted: Thu Mar 20, 2003 7:12 pm
by Igguana
What I'm trying to do is this, but in a single line:

Code: Select all

<?php
$pegar=mysql_query($query, $conexion) or die($query.' :'.mysql_error());
$pegar2=mysql_query($query2, $conexion) or die($query2.' :'.mysql_error());
?>
Should be excellent to do this in a single line too:

Code: Select all

<?php
while($row = mysql_fetch_array($pegar))
{
}
?>

Posted: Thu Mar 20, 2003 8:07 pm
by fractalvibes
I have found that using a format like this ( see below) will help in spotting errors in your sequel, and will be very readable and understandable when you have to look at it in 6 months from now....
Easy to spot mistakes, as everything is lined up, the a and b are shortcuts
known as correlation variables.



$somesql = "SELECT a.col1 "
. ", a.col2 "
. " , b.col1 "
. " , b.col3 "
. " FROM table1 a "
. ", table2 b "
. " WHERE a.col1 = b.col1 "
. " AND b.col3 = \"$someVar\" ";

Where $someVar is a value coming from a form or querystring, or wherever.

Phil J.