How to SELECT all fields from two tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

How to SELECT all fields from two tables?

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 />';
}
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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>";
	}
}
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

error message

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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:
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

still getting errors

Post 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)..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

could you please post your 'query' code along with the code you are using to display them on the screen?
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

code

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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>";
    }
}
?>
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

hmmm

Post 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)){
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

:oops: missed a semicolon needed on the end of line 22


Moved to Databases.
Last edited by feyd on Thu Aug 25, 2005 10:01 pm, edited 1 time in total.
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

got that, code ran but displayed blank screen...still poking at it...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it was blank, in the actual (HTML) source code?
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

yeah, nothing printed to the screen at all...viewed the source from the browser and nothing on the page...
Post Reply