How to SELECT all fields from two tables?
Moderator: General Moderators
How to SELECT all fields from two tables?
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
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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Code: Select all
SELECT * FROM customers
JOIN orders USING (customer_id)Code: Select all
while ($row = mysql_fetch_assoc($result)) {
echo $row['customer_id'].'<br />';
}- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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
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
"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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
change
to
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
Code: Select all
$result = mysql_query($result);Code: Select all
$result = mysql_query($result) or die(error_error());Btw, start wrapping your php code with [ php ] [ /php ] without the spaces
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
I still dont understand why you want to run an mysql_query command over mysql_query result, $resultJcart wrote:changetoCode: Select all
$result = mysql_query($result);
That will give you a hint to as of what the problem isCode: Select all
$result = mysql_query($result) or die(error_error());
Btw, start wrapping your php code with [ php ] [ /php ] without the spaces
still getting errors
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)..
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
code
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
andCode: 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]- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
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)){
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)){