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 "<h4> ", $row['Name'], " &nbsp; ", $row['Patent'], "</h4> ",$row['Common'], "<p> Height:  ", $row['Hname'], "<br> Spread:  ", $row['Sname'], "<br> Color:  ", $row['Color'], "<br> Light:  ", $row['Light'], "<br> Zone:  ", $row['Zone'], "<p> <img src=",$row['Picname'],  " /> <p><p>", $row['Notes'], "<p><p> <hr width='50%' size='1' color='#A3A3A3'><p>";
} 



$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 "<table border=0 cellspacing=2 cellpadding=0>";

Print "<font size=8 face='Arial'>";

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



?>

[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 = mysql_real_escape_string($_GET)$pullname = mysql_real_escape_string($_GET)

[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)