MySQL/PHP query

  peabody 21:57 30 Dec 2005
Locked

I have a MySQL db with 2 tables - 'fabrics' and 'suppliers'. The 'fabrics' table has field 'supplier_id' and the 'suppliers' table has the fields 'supplier_id' and 'supplier_name'.

I'm using Dreamweaver and have created 'fabrics' and 'suppliers' recordsets. I have created a php page to display selected records.

To display the 'supplier-id' from the 'fabrics' recordset, Dreamweaver generates the following code:

?php echo $row_fabrics['supplier_id']; ?>

But haw do I code to display the 'supplier_name' from the 'suppliers' recordset; i.e. link the recordsets on supplier_id in order to display the supplier_name?

I know this is probably fundamental stuff, but I'm new to php/MySQL etc.

I take it I am correct in using the recordsets and not trying to access the database tables directly?

Thanks. Please keep the answers simple!

  harristweed 11:16 31 Dec 2005

I'm not sure what Dreamweaver produces, but you appear to want the supplier_name that corresponds to the supplier_id. As you have the suppier_id already, it is quite simple the get the name from the supplier table.

The sequence of events is:
1. connect to database.
2. get the supplier name that corresponds to the supplier_id from the supplier table.
3. create a variable with the supplier name.
4. display the supplier name (echo the variable) at the correct place on your page.

Here's what the code should look like:

<?php

$db_host ="localhost";
$db_user="Your user name";
$db_password="Your password";
$db_database="The database name";
$supp_id=$row_fabrics['supplier_id'] ;

$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));

$select =" SELECT supplier_name FROM suppliers WHERE supplier_id = ' $supp_id ' ";
$results = mysql_query("$select", $link_id);
$query_data = mysql_fetch_row($results);
$supplier_name = $query_data[0];
?>

Now you have a variable called "$supplier_name" that contains the supplier name that corresponds to the supplier_id.

Hope this helps.

  peabody 11:24 31 Dec 2005

Thanks for the response.

I already have the DB link in place on the php page so will concentrate on the code to see the supplier_name.

Could you please just clarify: do I create recordsets and the php code looks at the recordsets, or does it process dirctly on the database tables?

I have the supplier_id dsplaying from a recordset created from the fabrics table. Do I create another recordset from the suppliers table and query that? Thanks.

  peabody 11:28 31 Dec 2005

I see the code:

$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));

which addresses the db direct, so my last question may have been unnecessary.

I'll try it all out. Thanks again.

  harristweed 18:51 01 Jan 2006

How are you getting on?

  peabody 21:16 01 Jan 2006

The page renders and displays the rows in the table, but the Supplier Name ($supplier_name) is just blank for all 6 rows (I set up 6 test db records). The other fields render AOK.

I added your code as follows:

<?php
$db_host ="localhost";
$db_user="root";
$db_password="********";
$db_database="rebecca_hazelrebecca";
$supp_id=$row_fabrics['supplier_id'] ;
$link_id = mysql_connect("$db_host","$db_user","$db_password");
if (mysql_select_db("$db_database", $link_id));
$select =" SELECT supplier_name FROM suppliers WHERE supplier_id = ' $supp_id ' ";
$results = mysql_query("$select", $link_id);
$query_data = mysql_fetch_row($results);
$supplier_name = $query_data[0];
?>

In the table cell to print the Supplier Name I inserted:

<?php echo($supplier_name);
?>

I mentioned that Dreamweaver generates the connection at the top of the page code as follows:

<?php require_once('Connections/hazelrebecca.php'); ?>

which refers to the connection configuration data held in a separate .php file as follows:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_hazelrebecca = "localhost";
$database_hazelrebecca = "rebecca_hazelrebecca";
$username_hazelrebecca = "root";
$password_hazelrebecca = "********";
$hazelrebecca = mysql_pconnect($hostname_hazelrebecca, $username_hazelrebecca, $password_hazelrebecca) or die(mysql_error());
?>

(I've blotted out my password here!!)

So, any ideas? As the connection is already set up, can I modify your code?

I really want to crack this so appreciate your help.

  harristweed 10:41 02 Jan 2006

I really need to see the code for the whole page.
If you would like me to help, please email it to me. My email address is available by clicking on the evenvelope icon.

I don't need the database passwords.

  harristweed 11:55 02 Jan 2006

Try changing

$select =" SELECT supplier_name FROM suppliers WHERE supplier_id = ' $supp_id ' ";

to....

$select =" SELECT supplier_name FROM suppliers WHERE supplier_id = \" $supp_id \" ";

And you dont need to bracket the variable..
<?php echo $supplier_name ; ?>

NOT

<?php echo($supplier_name); ?>

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?