multiple table mySQL query question

Question

Encrypted 0 Junior Poster in Training

14 Years Ago

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

mysql php

Edited 14 Years Ago by Encrypted because: n/a

4 Contributors
13 Replies
102 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by Encrypted

qazplm114477 14 Junior Poster

14 Years Ago

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...

Edited 14 Years Ago by qazplm114477 because: code tags isn't working

qazplm114477 14 Junior Poster

14 Years Ago

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?

qazplm114477 14 Junior Poster

14 Years Ago

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;.

LethargicCoder 0 Junior Poster in Training

14 Years Ago

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

Reply to this topic

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.

Encrypted 0 Junior Poster in Training · Answer 1 · 2010-04-25T09:00:23+00:00

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.

Encrypted 0 Junior Poster in Training · Answer 2 · 2010-04-25T09:46:44+00:00

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;

Encrypted 0 Junior Poster in Training · Answer 3 · 2010-04-25T09:56:27+00:00

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.

Encrypted 0 Junior Poster in Training · Answer 4 · 2010-04-25T10:28:23+00:00

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>

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.

qazplm114477 14 Junior Poster · Answer 5 · 2010-04-25T10:32:17+00:00

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?

Encrypted 0 Junior Poster in Training · Answer 6 · 2010-04-25T10:41:25+00:00

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
                
    ?>

qazplm114477 14 Junior Poster · Answer 7 · 2010-04-25T15:36:21+00:00

//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

}

diafol · Answer 8 · 2010-04-25T16:29:36+00:00

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"];
 }
}

Encrypted 0 Junior Poster in Training · Answer 9 · 2010-04-26T00:01:38+00:00

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.