Page 1 of 2

How to SELECT all fields from two tables?

Posted: Thu Aug 25, 2005 8:31 pm
by michlcamp
Question: Is there a way to SELECT * FROM two tables without specifying each field individually, ie:
customers.name, customers.address,customers.phone AND orders.product1, orders.product2, etc.?
table 1 : customers (has shipping address, phone number, etc)

table 2 : orders (has 9 products w/ fields for quantity ordered)

Can it be written something like: SELECT * FROM customers,orders WHERE customers.customer_id=orders.customer_id

Then how to display?
I'm displaying the order information on one side of an HTML table and the Ship to: information on the other side.

Wondering how to write the query and the WHILE loop...

any help tonight?
thanks in advance.
mc

Posted: Thu Aug 25, 2005 8:36 pm
by John Cartwright

Code: Select all

SELECT * FROM customers 
JOIN orders USING (customer_id)
Then just loop your rows normally..

Code: Select all

while ($row = mysql_fetch_assoc($result)) {
   echo $row['customer_id'].'<br />';
}

Posted: Thu Aug 25, 2005 8:44 pm
by raghavan20
hope this works :wink:

Code: Select all

//this would give you the details for an orderid and 
//the details of the customer who made that order
$query = "select * from
`Customers`, `Orders`
where
`Orders`.orderid = '$orderId'
and `Customers`.customerId = `Orders`.`customerId`";	

$result = mysql_query($query);
if (is_resource($result)){
	if (mysql_num_rows($result)){
		echo "<div style=\"width:600\">"
		while($row = mysql_fetch_array($result), MYSQL_ASSOC){
			echo "<div style=\"width:50%; float:left\"><span>$row["order_id"]</span>";
			echo "<br /><span>$row["product_details"]</span></div>";//add more spans
			echo "<div style=\"width:50%; \"><span>$row["customer_name"]</span>";
			echo "<br /><span>$row["shipping_address"]</span></div>";//you can put all the fields in separate <span>
		}
		echo "</div>";
	}
}

error message

Posted: Thu Aug 25, 2005 9:04 pm
by michlcamp
looks good but I'm getting this message:
"Supplied argument is not a valid MySQL result resource"

pointing at

while ($row = mysql_fetch_assoc($result)) {
echo $row['customer_id'].'<br />';
}

whole query looks like:

$dbh=mysql_connect ($host, $user, $password);
$link = $dbh;
@mysql_select_db($dbname) or die( "Unable to select database");
$query="SELECT * FROM orders2 JOIN customers USING (customer_id)";
$result=mysql_query($query);
mysql_close();
while ($row = mysql_fetch_assoc($result)) {
echo $row['customer_id'].'<br />';
}

sure would like to get this working...it's the last part of a six week project..a "Today's Orders" report for a client...

thanks again.
mc

Posted: Thu Aug 25, 2005 9:10 pm
by John Cartwright
change

Code: Select all

$result = mysql_query($result);
to

Code: Select all

$result = mysql_query($result) or die(error_error());
That will give you a hint to as of what the problem is

Btw, start wrapping your php code with [ php ] [ /php ] without the spaces

Posted: Thu Aug 25, 2005 9:12 pm
by raghavan20
Jcart wrote:change

Code: Select all

$result = mysql_query($result);
to

Code: Select all

$result = mysql_query($result) or die(error_error());
That will give you a hint to as of what the problem is

Btw, start wrapping your php code with [ php ] [ /php ] without the spaces
I still dont understand why you want to run an mysql_query command over mysql_query result, $result :roll:

still getting errors

Posted: Thu Aug 25, 2005 9:17 pm
by michlcamp
sure appreciate the help, but neither suggestions are working - getting parsing errors I can't see, and, well, I'm still learning the ropes (only been at php about six months)..

Posted: Thu Aug 25, 2005 9:20 pm
by raghavan20
could you please post your 'query' code along with the code you are using to display them on the screen?

code

Posted: Thu Aug 25, 2005 9:26 pm
by michlcamp

Code: Select all

<?php

include("host.php");
$dbh=mysql_connect ($host, $user, $password);
$link = $dbh;
@mysql_select_db($dbname) or die( "Unable to select database");
$query = "select * from `customers`, `orders2` where `orders2`.realname = '$realname'
and `customers`.customer_id = `Orders`.`customer_id`";

$result = mysql_query($query);
if (is_resource($result)){
    if (mysql_num_rows($result)){
        echo "<div style=\"width:600\">"
        while($row = mysql_fetch_array($result), MYSQL_ASSOC){
            echo "<div style=\"width:50%; float:left\"><span>$row["realname"]</span>";
            echo "<br /><span>$row["completeset"]</span></div>";//add more spans
            echo "<div style=\"width:50%; \"><span>$row["realname"]</span>";
            echo "<br /><span>$row["shaddress"]</span></div>";//you can put all the fields in separate <span>
        }
        echo "</div>";
    }
}
?>

feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Thu Aug 25, 2005 9:47 pm
by feyd
fixed up:

Code: Select all

<?php

include("host.php");
$dbh=mysql_connect ($host, $user, $password);
$link = $dbh;
@mysql_select_db($dbname) or die( "Unable to select database");
$query = "select * from `customers`, `orders2` where `orders2`.realname = '$realname'
and `customers`.customer_id = `Orders`.`customer_id`";

$result = mysql_query($query) or die(mysql_error());
if (is_resource($result)){
    if (mysql_num_rows($result)){
        echo "<div style=\"width:600\">"
        while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
            echo "<div style=\"width:50%; float:left\"><span>{$row["realname"]}</span>";
            echo "<br /><span>{$row["completeset"]}</span></div>";//add more spans
            echo "<div style=\"width:50%; \"><span>{$row["realname"]}</span>";
            echo "<br /><span>{$row["shaddress"]}</span></div>";//you can put all the fields in separate <span>
        }
        echo "</div>";
    }
}
?>

hmmm

Posted: Thu Aug 25, 2005 9:55 pm
by michlcamp
Boy, thanks for your time tonight. Just can't seem to get it working, though...

got this message:
Parse error: parse error, expecting `','' or `';'' in /www/secure/get_orders.php on line 23


line 23: while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

Posted: Thu Aug 25, 2005 9:57 pm
by feyd
:oops: missed a semicolon needed on the end of line 22


Moved to Databases.

Posted: Thu Aug 25, 2005 10:01 pm
by michlcamp
got that, code ran but displayed blank screen...still poking at it...

Posted: Thu Aug 25, 2005 10:03 pm
by feyd
it was blank, in the actual (HTML) source code?

Posted: Thu Aug 25, 2005 10:09 pm
by michlcamp
yeah, nothing printed to the screen at all...viewed the source from the browser and nothing on the page...