using JOIN to grab plant description and plant availability

[AngieS]

I am trying to create a plant reference guide where the customer can grab plant description from one table (findplantsdb) and the availability from another table (plantsdb). I am new to PHP and I think I am close, but what is happening is that the plant description is coming up, but ALL of the plant availability for ALL the plants is coming up too. I need a particular plant that the customer selects from a form to show its description from table-findplantsdb and its availabililty from table-plantsdb.

NOTE: 'pullname' is the plant nsme that the customer selects.

Can anyone help me?

// Query
$query= "SELECT * FROM findplantsdb WHERE Name='" . $_GET['pullname'] . "'";

$result = mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);

echo " ", $row['Name'], "   ", $row['Patent'], " ",$row['Common'], " Height: ", $row['Hname'], "
Spread: ", $row['Sname'], "
Color: ", $row['Color'], "
Light: ", $row['Light'], "
Zone: ", $row['Zone'], "

", $row['Notes'], "

";
}

$data= mysql_query("SELECT plantsdb.name, plantsdb.type, size, yard, heelin, nheel, field, total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name") or die(mysql_error()); Print "

";

Print "";

while($info = mysql_fetch_array( $data )) { Print ""; Print "".$info['name'] . " "; Print "- Size: ".$info['size'] . " "; Print " - Type: ".$info['type'] . " "; Print " - Yd: ".$info['yard'] . " "; Print " - Heel: ".$info['heelin'] . " "; Print " - NH: ".$info['nheel'] . " "; Print " - Fld: ".$info['field'] . " "; Print " - Total: ".$info['total'] . " "; } Print "";

?>

[jkon]

Hello , first of all for you own security don’t use MySQL like that. PHP has PDO that provide a lot of usability’s and security. One the other hand if you insist using mysql_query than just use mysql_real_escape_string in the field
http://php.net/manual/en/function.mysql-real-escape-string.php .
I am starting with that because as your script is anyone can easily blow up your database or even your server.

So lets say the

$_GET['pullname']

is

$pullname = mysql_real_escape_string($_GET['pullname'])

and your statement as described is

SELECT plantsdb.name, plantsdb.type, size, yard, heelin, nheel, field, total FROM plantsdb LEFT JOIN findplantsdb ON plantsdb.name=findplantsdb.Name

. As you can see there isn’t WHERE clause in that statement so it brings all results …. Just add

WHERE plantsdb.name = $pullname

*Really take a look at PDO it make take you one or two days to master it but will make your life a lot easier.

[diafol]

Ok, few things:
1st: use code tags to show code
2nd: you need to give your tables better names
3rd: you need to find a simpler way to echo out your output.

Something like this, maybe:

$pullname = mysql_real_escape_string($_GET['pullname']);
$data= mysql_query("SELECT findplantsdb.*, plantsdb.* FROM findplantsdb AS f #### JOIN plantsdb AS p ON f.Name=p.name WHERE f.Name='$pullname'") or die(mysql_error());

Where #### is the type of join you need LEFT, INNER, RIGHT being the usual suspects.

//@jkon - simultaneous post

[AngieS]

I'm sorry, I had to learn this in a week and I'm just so new at this. I thank you both for your help and commend you on how much you know on this subject. I will continue to learn as I do find this fasinating.

But, now to sound dumb again - in order to stay safe, is this how it should look now:

// Query
$query= "SELECT * FROM findplantsdb WHERE Name='" . $_GET['pullname'] . "'";

$pullname = mysql_real_escape_string($_GET['pullname'])$pullname = mysql_real_escape_string($_GET['pullname'])

[jkon]

Don’t be sorry, no one learned a lot from first day… stick to code that ardav provided and your code will not be so vulnerable. (safety is a big chapter but in most cases these simple rules will work)