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

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

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