Hello, I'm trying to pull data from multiple tables in a single mysql_query and I'm having trouble displaying the data that's pulled. Here's what I've got so far.. the highlighted code is the part that doesn't work.
(sorry in advanced for the sloppy code.. i've been copying/pasting stuff all over the place)

$getProducts = "SELECT t1.title, t2.amount, t1.id, t2.id FROM feelist t1, fees t2 WHERE t2.uid = '$user[id]'";
	if (!$productResult = mysql_query($getProducts))
	{
		// There was an error in the sql statement
		print "there was an error in the sql statement, ".mysql_error()."<br><b>$getProducts</b>";
		exit;
	}
	else
	{
            ?>
                <form action="<? echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post"> 
            <br />
            
            <div class="shadowDivider">&nbsp;</div>
            <?

	while ($productRow = mysql_fetch_array($productResult)){

	    if ($user[account] == 1){
                
        ?>
            
            <div class="formRow">
           	<label><? echo $productRow["t1.title"]; ?>:</label>
              <input name="<? echo $productRow["t1.id"]; ?>" value="<? echo $productRow["t2.amount"]; ?>" />
            </div><!--end formRow-->
    <?
            } //end if user is level 1
	} //end while
} //end if-else

Try this

//concat the field properly as added alias
$getProducts = "SELECT t1.title as t1_title, t2.amount as t2_amount, t1.id as t1_id, t2.id as t2_id FROM feelist t1, fees t2 WHERE t2.uid = '".$user[id]."'";

//NOTE: try "echo $getProducts;" to check if the sql statement ran properly, run the query printed out in the webpage on mysql

$productResult= mysql_query($getProducts );
        //use num_rows to validate
	if (mysql_num_rows($productResult)==0)
	{
		// There was an error in the sql statement
		print "there was an error in the sql statement, ".mysql_error()."<br><b>$getProducts</b>";
		exit;
	}
	else
	{
            ?>
                <form action="<? echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post"> 
            <br />
            
            <div class="shadowDivider">&nbsp;</div>
            <?
           //add MYSQL_BOTH
	while ($productRow = mysql_fetch_array($productResult, MYSQL_BOTH)){

	    if ($user[account] == 1){
                
        ?>
            
            <div class="formRow">
           	<label><? echo $productRow["t1_title"]; ?>:</label>
              <input name="<? echo $productRow["t1_id"]; ?>" value="<? echo $productRow["t2_amount"]; ?>" />
            </div><!--end formRow-->
    <?
            } //end if user is level 1
	} //end while
} //end if-else

I'm not sure how your database is built, but you may want to try using JOINS instead of from tbl1, tbl2 where etc...

That didn't help ^. Last time I checked, you could use underscores in variable names, so would that be looking for a row that's literally called "t1_id" in the table?

Anyway, any other suggestions?


Edit: I missed the "as" statement in the query.. I'll try that now.

Update: No, still not working.

That didn't help ^. Last time I checked, you could use underscores in variable names, so would that be looking for a row that's literally called "t1_id" in the table?

Anyway, any other suggestions?


Edit: I missed the "as" statement in the query.. I'll try that now.

Update: No, still not working.

If you echo $getProducts and run it in mysql itself does it bring back the correct data?

is there an error message?

If you echo $getProducts and run it in mysql itself does it bring back the correct data?

is there an error message?

Like this..?

$getProducts = "SELECT title, id FROM feelist";

That runs fine ^.

Doing this

$getProducts = "SELECT feelist.title, fees.amount, feelist.id, fees.id AS fees_id FROM feelist, fees WHERE fees.uid = '$user[id]'";
echo mysql_query($getProducts);

prints

Resource id #7

This doesn't print anything.. I'm not sure if it's supposed to.

while ($productRow = mysql_fetch_array($productResult)){ 
 echo $productRow;

Like this..?

$getProducts = "SELECT title, id FROM feelist";

That runs fine ^.

Doing this

$getProducts = "SELECT feelist.title, fees.amount, feelist.id, fees.id AS fees_id FROM feelist, fees WHERE fees.uid = '$user[id]'";
echo mysql_query($getProducts);

prints

just echo $getProducts;.

just echo $getProducts;.

That just prints:

SELECT feelist.title, fees.amount, feelist.id, fees.id AS fees_id FROM feelist, fees WHERE fees.uid = '$user[id]'"

All $getProducts is, is a String that gets inserted into mysql_query... so there's no reason that wouldn't print anything.

Hello, I'm trying to pull data from multiple tables in a single mysql_query and I'm having trouble displaying the data that's pulled. Here's what I've got so far.. the highlighted code is the part that doesn't work.
(sorry in advanced for the sloppy code.. i've been copying/pasting stuff all over the place)

$getProducts = "SELECT t1.title, t2.amount, t1.id, t2.id FROM feelist t1, fees t2 WHERE t2.uid = '$user[id]'";
	if (!$productResult = mysql_query($getProducts))
	{
		// There was an error in the sql statement
		print "there was an error in the sql statement, ".mysql_error()."<br><b>$getProducts</b>";
		exit;
	}
	else
	{
            ?>
                <form action="<? echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post"> 
            <br />
            
            <div class="shadowDivider">&nbsp;</div>
            <?

	while ($productRow = mysql_fetch_array($productResult)){

	    if ($user[account] == 1){
                
        ?>
            
            <div class="formRow">
           	<label><? echo $productRow["t1.title"]; ?>:</label>
              <input name="<? echo $productRow["t1.id"]; ?>" value="<? echo $productRow["t2.amount"]; ?>" />
            </div><!--end formRow-->
    <?
            } //end if user is level 1
	} //end while
} //end if-else

Two things I would look at first:

1. In your query, you are not joining feelist and fees. Is this correct? You'll be retrieving the product of every row in feeslist and every row in fees matching the uid.

2. Remove the table alias from your echo statements.

<div class="formRow">
           	<label><? echo $productRow["title"]; ?>:</label>
              <input name="<? echo $productRow["id"]; ?>" value="<? echo $productRow["amount"]; ?>" />
            </div><!--end formRow-->

Two things I would look at first:

1. In your query, you are not joining feelist and fees. Is this correct? You'll be retrieving the product of every row in feeslist and every row in fees matching the uid.

2. Remove the table alias from your echo statements.

<div class="formRow">
           	<label><? echo $productRow["title"]; ?>:</label>
              <input name="<? echo $productRow["id"]; ?>" value="<? echo $productRow["amount"]; ?>" />
            </div><!--end formRow-->

Already did both of those.. What I'm trying to do is pull the "title" and "id" from feelist, which is what is displayed in a form. Then I match the "id" from feelist with the id in fees and pull the corresponding "amount" in fees.

if your $getProducts is correct then the problem may lie somewhere in your echo $productRow["t1.title"]

if your selected statement has this SELECT t1.title AS t1_title FROM etc...
you have to use

echo $productRow["t1_title"];
instead.

is there any kind of error message what so ever?

if your $getProducts is correct then the problem may lie somewhere in your echo $productRow["t1.title"]

if your selected statement has this SELECT t1.title AS t1_title FROM etc...
you have to use

echo $productRow["t1_title"];
instead.

is there any kind of error message what so ever?

That's what I've been trying but nothing shows up. No error messages, no nothing.

This is the exact code I'm using.. even if I take out the WHERE fees.uid = ..... I still get nothing.

$getProducts = "SELECT feelist.title AS title, fees.amount AS amount, feelist.id AS id, fees.id AS fees_id FROM feelist, fees WHERE fees.uid = '$user[id]'";
    echo mysql_query($getProducts);
	if (!$productResult = mysql_query($getProducts))
	{
		// There was an error in the sql statement
		print "there was an error in the sql statement, ".mysql_error()."<br><b>$getAppraisers</b>";
		exit;
	}
	else
	{
            ?>
                <form action="<? echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post"> 
            <br />
            
            <div class="shadowDivider">&nbsp;</div>
            <h1>Product Fees</h1>
            <p class="required">Do not enter currency.  Example: for $125 enter 125.</p>

            <?
        $i=1;
	while ($productRow = mysql_fetch_array($productResult)){
		//sort by dist
		//enter new id's & dist into matrix or parrallel arrays
		//after while loop, loop through matric or arrays and display appraisers in order
?>
	<?
	    if ($user[account] == 1){
            echo $productRow;    
        ?>
            
            <div class="formRow">
           	<label><? echo $productRow["title"]; ?>:</label>
              <input name="<? echo $productRow["id"]; ?>" value="<? echo $productRow["amount"]; ?>" />
            </div><!--end formRow-->
    <?
            } //end if user is Appraiser
	} //end while
                
    ?>
//change this 
if (!$productResult = mysql_query($getProducts))

//to this 
$productResult = mysql_query($getProducts);
if(mysql_num_rows($productResult)<=0){
//error
//mysql_num_rows() returns the number of rows in a result set on success or FALSE on failure. 
}
else{

//do code

}
Member Avatar for diafol

This is the full general syntax:

SELECT t1.field2 AS t1f2, t2.field2 AS t2f2, (etc) FROM table1 AS t1  INNER JOIN table2 AS t2 ON t1.field1 = t2.field3 WHERE t1f2 = '$myvalue'

Note that I've used aliases throughout just as an example so that there no 'duplicates' field names like 'id'. This is overkill, but may be useful for now.

Your code may be missing the all-important join fields. Although you have two id fields - are they the ones that are actually linked? Have you tried to link the PK values of fees with the PK values of the other table? If so, this is probably the wrong join.

Anyway:

$result = mysql_query("SELECT...");
//errorcheck - then
if(mysql_num_rows($result) > 0){
 while($data = mysql_fetch_array($result)){
   echo $data["t1f2"];
 }
}

This is the full general syntax:

SELECT t1.field2 AS t1f2, t2.field2 AS t2f2, (etc) FROM table1 AS t1  INNER JOIN table2 AS t2 ON t1.field1 = t2.field3 WHERE t1f2 = '$myvalue'

I think this should work.. what does the following code do? I can't find an explanation online anywhere..

ON t1.field1 = t2.field3

Edit: This worked, thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.