order history stumped and run aground

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

order history stumped and run aground

Post by jramaro »

hi,
I'm re-doing a 'view order history' since the old way was more of a manual process as opposed to automated.

Anyway, I've come to a point i've not used before in php . might sound odd, but i havent.

I have a user_order_history SQL db
I can echo it back and its all working great as long as it only makes one user_id call.

I need it to display a formatted output for each indvidual order history block of information.
So that user can see every block of order history with date and it is seperated to be neat and not all running in
one mass.

I've looked over the codes and php.net and not coming across the way to finish this code, not sure if this is what i need.

here's what i have so far

Code: Select all

<?php 
session_start(); 
$_SESSION['user_id'] = 34 ; 


require('connect/mysql_connect.php');

$sql = "SELECT * FROM user_order_history  WHERE user_id = " . $_SESSION['user_id'] ;
$res = mysql_query($sql); 
$row = mysql_fetch_assoc($res); 


// set variables. 
$user_id = $_SESSION['user_id']; 
$product1 = $row['product1']; 
$attribute1 = $row['attribute1']; 
$attribute2 = $row['attribute2']; 
$attribute3 = $row['attribute3']; 
$attribute4 = $row['attribute4']; 
$date  = $row['date'];


//echo variables to see they're working. 
echo $product1; 
echo $attribute1 ;
echo $attribute2 ;
echo $attribute3 ;
echo $attribute4 ;
?>



<?php 
// TROUBLE SPOT 
$order_history_items = count($row['user_id']);
$total = 0;

if ($items == 0){
	echo "There are currently no items in your Order history.";
} else {

	for ($cnt=0; $cnt < $items; $cnt++)

   // ????? run out of brain juice
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$items is never set in the code posted.

You will require a loop to extract each result from the query. There are large numbers of examples of this online.

You may also want to look at the first to threads linked from Useful Posts (a sticky found in PHP - Code, and linked from my signature.)
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

thanks,

I figured out how to display all records , but problem i have now is
I'm saving order history information in numeric representation.

so for example a product that normally has 3 or 4 details like 1. name 2. baseprice 3. weight etc
in the normal product database. the products are listed with product id 1 through say 100

each number will relate to a different product and all the details

so anyway i make the order history save the product ID and not serialize .

i call the order history with:

Code: Select all

// Make the query.
$query= "SELECT * FROM user_order_history WHERE user_id  = " . $_SESSION['user_id']; 
$result=mysql_query ($query); // Run the query.
$num=mysql_num_rows($result);

if ($num > 0) { // If it ran OK, display the records.

echo "<p>There are currently $num order histories.</p>\n";
}


// Fetch and print all the records.
while ($row=mysql_fetch_array($result, MYSQL_ASSOC)) {
              echo $row['product_type ']

// i make the product _type to a variable to be used later. (probably part of the problem? )
$product_type = $row['product_type'] ;}


This all echos out good , all the seperate order history numeric representations are there.
Then i have to convert the numeric representations to the real details.
So i take the product_type number and do a call to database to fetch that cooresponding product.

Code: Select all

$query= "SELECT * FROM products  WHERE id = " . $product_type ; 
$result=mysql_query ($query); // Run the query.
$num=mysql_num_rows($result);

// Fetch and print all the records.
while ($row=mysql_fetch_array($result, MYSQL_ASSOC)) {

echo "1.<b> Product Name:</b>" . " "  . $row['name1'] . "<BR>";
echo "2.<b> Quantity:</b>" . " "  . $row['quantity'] . "<BR>";
echo "3.<b> baseprice:</b>" . " " .$row['baseprice'] . "<BR>";
echo "4.<b> weight:</b>" . " " .$row['weight'] . "<BR>";
}


It works but it only displays the last record / only one.
Is there a way to do it this way , yet apply the different original numeric representations to this second query t ocall and display all those records ?

thank you :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can use a JOIN query, specifically an INNER JOIN. There are many examples of these types of queries in the Databases board or on sites like w3schools.com as well as across the web.
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

thanks Feyd,
I used an inner join and it works great! .. too great actually because it gets all the records lol

how can i narrow it down to specific records ?
here's what i have

Code: Select all

// Make the query.
$query= "SELECT products.name1, products.quantity, products.baseprice, products.weight, user_order_history.shipping_price, user_order_history.date
FROM products
INNER JOIN user_order_history 
ON products.id = user_order_history.product_type "; 

$result=mysql_query ($query); // Run the query.
$num=mysql_num_rows($result);



It's working and echoing out in the correct format too, but its pulling all the records out.
I'd like it to be specific where a user_id that is set in $_SESSIONS from login is the control number.

so that the process would be
$_SESSION user_id --> match to user_order_history database user_id --> grab only those records --> match user_order_history product ids to the products database to get the complete info .

is that possible?

I've tried many different ways but its taking a $_SESSION user_id and accidently using it as the product details . so example if user_id is 34 , its matching product_id to 34 ..and sending back that product.
The way i have posted above works and doesnt confuse the records, but it grabs ALL of them

Thank You
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

By adding a WHERE clause, like before, that specifies you want only "x" user.
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

im trying to put that WHERE clause in
and it aint workin for me

it keeps breaking it and saying = not a valid MYSQL database resource.

I've put it in everywhere , even the seemingly ignorant places to put it.
does the syntax have to change any for that?

my syntax is

Code: Select all

WHERE user_id =" . $_SESSION['user_id'] .
so if an example

Code: Select all

// Make the query. 
$query= "SELECT products.* (<-- tried here) , user_order_history.* (<-- here) 
FROM products 
INNER JOIN user_order_history (<-- here ) 
ON products.id = user_order_history.product_type (<-- and here)  "; 

nuffin
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It would go at the very end. If you echo mysql_error(), what does it tell you?
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

it says "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource "

I'm gettin sorta turned around in it i think, because if i was to use the WHERE clause there it would grab the product from the $SESSION user_id
and that wouldnt be right.

what im trying to do , which i think i might be going about it on a sorta veering off course sorta way , is

I have a products database, a user database, user order history database, and product attributes databases ( about 4 of them)

the user databse has the typical user_id with the user email and information.
the products has product id numbers for primary key. products are listed in cliche 1 through say 100 order.

then the user order history, when they order it sends
the order info into a database like this :

order_history_id | user_id | product_type | att1 | att2 | att3| att4 | att5 | att 6 | att7 | date
-------------------------------------------------------------------------------------------------------------------------------------------
basic numbering | (ex.) 34 | (ex) 3 |(ex) 2 | 3 | 1 | 2 | 2 | 1 | 3 | 2007-08-20 13:12:12
-----------------------------------------------------------------------------------------------------------------------------------------------



and what I'm tyring to get , is so that when a user is logged in and the user_id is put into $_SESSIONS .
then when they click their ordre history, their user_id is used (example 34 )
it takes that and would select all order histories for the user_id of 34 (which is basic so far).

but it would grab all those attribute numbers as well. Then , using the attribute numbers to make calls to at least 4
seperate tables so that each attribute is selected.

so for example if attribute1 database is like this :

product_id | product_name | price | weight |
-------------------------------------------------------------
2 | Fancy product | $54.32 | 12.00 |
--------------------------------------------------------------


so the product_id and the att1 number will match ..and grab all that info .
and I'd need it to do that across at leats 4 seperate instances.

So since im using more than one inner join technically, am i going about it the right way?

thank you
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Well, you'll be doing some more joins is all.. but it's still the same concept. And you didn't echo mysql_error(). ;)
jramaro
Forum Commoner
Posts: 58
Joined: Tue Jun 26, 2007 7:46 am

Post by jramaro »

will i make a seperate unique SQL query for each different join ?

or do i put them all into one query in some alien fancy way ? :)

oh and i havent figured out how to make only the user_id records show yet
its dumpin the whole order history database on me lol
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

jramaro wrote:will i make a seperate unique SQL query for each different join ?

or do i put them all into one query in some alien fancy way ? :)
The latter, generally.
jramaro wrote: oh and i havent figured out how to make only the user_id records show yet
its dumpin the whole order history database on me lol
Okay, lets try this from a different angle: take your original query against user_order_history that used user_id. Modify it to INNER JOIN the products table as needed.
Post Reply