I've got a scope issue a little different from the one below. I've got a page about a product. The id is appended in the url then the script queries the db for all fields for that id. Now I want to build a second query to pull more products similar to the one displayed. But the variables aren't passing to the include. The initial query is for the id - but it calls all the fields. So I should be able to build a second query using one of those fields - ie. "you might like these things that are also blue" or whatever. $id = $_REQUEST['id']; $sql = "SELECT * FROM `my_table` WHERE `id`='$id'"; $result_array = $DB->query($sql); Code (markup): I want to add something like: $sql = "SELECT * FROM `my_table` "; $sql .= "WHERE category LIKE '%"$result_array[0]['category']."%' "; Code (markup): Do I have to set a global variable for the fields after the original query so that they are available to define the second query? It's basically the exact same query - but I can't figure out how to get the variable from the first into the second. One's pulled from the URL, the other's pulled from the resulting array. If what I am missing is readily apparent to anyone, I would be grateful for the help.
You basically have it right. YOu can print out all the contents of $result_array to see how it's set up if you want echo '<pre>'; print_r ($result_array); echo '</pre>'; PHP: Also make sure you sanitize whatever variables are passed in the URL. This would work fine $id = mysql_real_escape_string ($_REQUEST['id']); PHP:
Thanks Exam. I stumbling through this and spend a lot of time wondering if I'm wondering down the absolutely wrong road. I appreciate the input.
Here I am again. Still hitting a wall here. The second part of the query is still not working - print_r($item_array) returns an empty array; the same function for $result_array returns the array. The problem has to be in my syntax in passing the variable($result_array[0]['productclass']) to the second query because I can echo that variable and it prints the processed value. And I've tried the query without a variable - just "productclass=blue widgets" or whatever. I've tried putting the first SELECT statement - the one that definitely works - into the second query. But nothing works in that second SELECT statement. I've tried every variation of quotes - single with concatenation operator, double w/o concat. op.. I've tried passing it without defining it as $productclass, with and without {}. I've pretty much tried every conceivable variation I think of, and then made some up. The amount of time I've spent on this single line is embarassing. Is there anythere here that's glaringly wrong? $id = $_REQUEST['id']; $id = mysql_real_escape_string ($_REQUEST['id']); $sql = "SELECT * FROM my_table WHERE id='$id'"; $result_array = $DB->query($sql); { $productclass = $result_array[0]['productclass']; $item_sql = "SELECT * FROM my_table WHERE productclass='$productclass'"; $item_array = $DB->query($item_sql); } Code (markup):
im thinking you forget to make a while loop? also is $id numeric make sure to check that as its nice for sql injection like this $id = intval($_REQUEST['id']); $sql = "SELECT * FROM my_table WHERE id='".mysql_real_escape_string($id)."'"; // you forget to make the while loop here while($row = $DB->query($sql)){ $result_array[]=$row } { $productclass = $result_array[0]['productclass']; $item_sql = "SELECT * FROM my_table WHERE productclass='$productclass'"; while($row2 = $DB->query($item_sql)){$item_array[]=$row2;} } PHP:
Try plugging this in and running it. See what gets printed out, it will help us identify where the problem is. Hopefully there's no typos in it (I didn't test it) <?php define ('DEBUG', 'lets_debug'); // Sanitize ID $id = mysql_real_escape_string ($_REQUEST['id']); // Query for this product based on ID $q = "SELECT * FROM `my_table` WHERE `id`='$id'"; $result = $DB->query ($q); // Print debug info if (defined (DEBUG)) { echo '<DIV CLASS="background-color:#FFDDDD; border:1px solid #FF0000;">QUERY: '.$q.'<br />'; echo 'MYSQL ERROR: '.mysql_error ().'<br />'; echo 'MYSQL ERROR NUMBER: '.mysql_errno ().'</div>'; } // I'm assuming that ID is unique, and will always return ONE product. $product = mysql_result ($result, 0); // Print debug info if (defined (DEBUG)) { echo '<DIV CLASS="background-color:#FFDDDD; border:1px solid #FF0000;">'; echo 'PRODUCT: <pre>'; print_r ($product); echo '</pre></div>'; } // Query for related products based on product class $productclass = $product['productclass']; // Print debug info if (defined (DEBUG)) { echo '<DIV CLASS="background-color:#FFDDDD; border:1px solid #FF0000;">'; echo 'PRODUCTCLASS: '; echo $productclass; echo '</div>'; } $q = "SELECT * FROM `my_table` WHERE `productclass`='$productclass'"; $result = $DB->query ($q); // Print debug info if (defined (DEBUG)) { echo '<DIV CLASS="background-color:#FFDDDD; border:1px solid #FF0000;">QUERY: '.$q.'<br />'; echo 'MYSQL ERROR: '.mysql_error ().'<br />'; echo 'MYSQL ERROR NUMBER: '.mysql_errno ().'</div>'; } // Save related products in array $related_products = array (); while ($one = mysql_fetch_array ($result)) { $related_products[] = $one; } // Print debug info if (defined (DEBUG)) { echo '<DIV CLASS="background-color:#FFDDDD; border:1px solid #FF0000;">'; echo 'RELATED PRODUCTS: <pre>'; print_r ($related_products); echo '</pre></div>'; } ?> PHP:
First, thanks for all the help. Now I getting two warnings: mysql_result(): supplied argument is not a valid MySQL result resource in and mysql_fetch_array(): supplied argument is not a valid MySQL result resource in Now the line numbers have changed from above so the first warning is line 20 above: // I'm assuming that ID is unique, and will always return ONE product. $product = mysql_result ($result, 0); Code (markup): The second is line 53 above: while ($one = mysql_fetch_array ($result)) { Code (markup): The funny part is, that was the part that did work. It generated an array for the product id passed in the url, echoed fields from the array and then created a new variable from one of the fields. But I wasn't ever able to get that variable into the second query. Now it seems to have changed its mind. Anway, thanks again.
OK, that means one of two things. There is a problem in the SQL sytax and no results are returned, or mysql found 0 rows that match the criteria. Was there any mysql error?
its very simple. But as you didnt look at my code in the first place, and that what exam written is too much of the good i think ( not to offend but i just think with all that debug and div stuff ,for just 2 basic querys is too much of the good) nyway, you dont have access to it as you closed the while query... while{ // do something here with them } // here they are gone and that happen to you so you need to put them in an array, then u can get to them outside the while query while($row=mysql_fetch_array($query)){ $myarray[]=$row; } echo '<pre>'. print_r($myarray).'</pre>'; Hope this clarifies
Isn't that exactly what I did? // Save related products in array $related_products = array (); while ($one = mysql_fetch_array ($result)) { $related_products[] = $one; } PHP:
I did look at both code snippets, and tried them both. If I understand correctly, you're saying that the array from the first query is gone by the time I execute the second? The WHILE will give me access to the first array from the second query. That would make sense I guess - it explains everything that I've seen. The missing variable did definitely exist - just not outside the query. I apologize for my slow-wittedness and I am grateful for the help. Cheers.