bhavin_85 Posted March 19, 2007 Share Posted March 19, 2007 hi guys ive written this query but its returning the invoice id and not the item_item_id <? session_start(); if ( empty($_SESSION['username'])){ header("location:default.php"); exit; } $id=$_SESSION['cust_id']; include ('config.php'); $sql = "SELECT invoice_id FROM invoices WHERE cust_id='$id'"; $query = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_assoc($query)) { $invoice_id = $row['invoice_id']; $sql1 = "SELECT i.item_item_id, COUNT(*) as total FROM invoices v INNER JOIN invoice_items i ON v.invoice_id = i.invoices_invoice_id WHERE i.invoices_invoice_id='$invoice_id' GROUP BY i.item_item_id ORDER BY total DESC LIMIT 1"; $query1 = mysql_query ($sql); $most_frequent_id = mysql_result ($query1,0); } ?> im guessing there is something wrong with the second query but i thought thats how u use joins the way it works is, the first query goes through the invoices table and selects all the invoice_id where the cust_id = the current customer, then for each of those invoice_id's it runs through the invoice_items table and counts the item_item_id...the final output should be the the most frequent item_item_id associated with all the invoices for that customer your help is much appreciated Link to comment https://forums.phpfreaks.com/topic/43341-query-returning-the-wrong-result/ Share on other sites More sharing options...
per1os Posted March 19, 2007 Share Posted March 19, 2007 $query1 = mysql_query ($sql1); $most_frequent_id = mysql_result ($query1,0); Yea, you were referencing $sql instead of $sql1 Link to comment https://forums.phpfreaks.com/topic/43341-query-returning-the-wrong-result/#findComment-210505 Share on other sites More sharing options...
bhavin_85 Posted March 19, 2007 Author Share Posted March 19, 2007 i got that sorted, any 1 know how i could do this though i want a page run through all the images in the database and show them...then when the user clicks on the image it takes them to a form so they can upload a replacement picture. ive got it to print 1 image so far but not sure how to make it print all pictures thats the code that gets the image <? include ('../config.php'); $sql = "SELECT image FROM images"; $query = mysql_query($sql) or die(mysql_error()); $image = mysql_result($query,0,"image"); header("Content-type: image/png"); echo $image; ?> this is the page that displays the pics <? session_start(); if ( empty($_SESSION['username'])){ header("http://localhost/PBL/default.php"); exit; } include ('../config.php'); $sql = "SELECT image_id, description, item_item_id FROM images"; $query = mysql_query($sql) or die(mysql_error()); ?> <html> <head> <title>Administrator - Promo Search</title> </head> <body> <table width="650" align="center"> <tr> <td align="right"> <? echo "You are logged in as " . $_SESSION['uname'] . ""?> <a href="../default.php"> Logout</a></td></tr> <tr> <td align="center"><b> Set Promotion</b></td> </tr> <tr> <td> </td> </tr> <tr> <td> <? while($row = mysql_fetch_assoc($query)) { $image_id = $row['image_id']; $description = $row['description']; $item_item_id = $row['item_item_item']; echo $image_id; } ?> </td> </tr> <tr> <td> </td></tr> </table> </body> </html> Link to comment https://forums.phpfreaks.com/topic/43341-query-returning-the-wrong-result/#findComment-210517 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.