[SOLVED] Simple Function Problem

[shayster]

Okay first of all I am new and this is my first function. I borrowed most of the code but then tried to change it to suit my needs. The function is suppose to receive an id and then use it in the function to join two tables and then display the results in a table. I get the Table and the field names but can't get the results from the table. I added a line to print the id to assure the function was getting it and it was. I also ran the query through mysql directly and got the proper results. Thanks in advance:

 

function orderidToEditOrder($orderid){
  //given a table name, generates HTML table including
  //edit buttons

  $orderid = filter_input(INPUT_POST, "orderid");
  $orderid = mysql_real_escape_string($orderid);

  global $dbConn;
  $output = "";
  $query = "select category.cat_name, orders.wgt_ounces, orders.del_date from orders, category where orders.id='$ordersid' and category.id=orders.cat_id";

  $result = mysql_query($query, $dbConn);

  $output .= "<table border = '1'>\n";
  //get column headings

  //get field names
  $output .= "<tr>\n";
  while ($field = mysql_fetch_field($result)){
    $output .= "  <th>$field->name</th>\n";
  } // end while
//add empty columns for edit
  $output .= "<th width=70></th>\n";
  $output .= "</tr>\n\n";

  //get row data as an associative array


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){

    echo "<tr>\n";

$output .= "  <td>Item: {$row['category.cat_name']} </td>";
$output .= "<td>Weight in Ounces: {$row['orders.wgt_ounces']} </td>";
$output .= "<td>Delivery Date: {$row['orders.del_date']} </td></tr>";



}
$output .="</table>";
return $output;

} // end orderidToEditOrder

[.josh]

$result = mysql_query($query, $dbConn) or die(mysql_error());

 

any errors pop up?

[shayster]

Nope.

 

The error is definitely in the second  while statement. Is it because I am trying to while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) after doing a  while ($field = mysql_fetch_field($result))? Do I have to separate them some how?

[haku]

You are using $results twice. You can't do that the way you are doing it. Arrays have a pointer that points at the 'current' element of the array. So after the first while loop, the pointer is at the end of the array, so it finds no values for the second loop.

 

Before your second loop, try this code:

 

reset($result);

 

and see what happens.

[shayster]

Yeah that's what I thought. I tried reset($result); but got:

 

Warning: reset() [function.reset]: Passed variable is not an array or object in

[haku]

Ahh, sorry my bad. Query results aren't arrays, so that won't work.

 

Ok, there are two ways I have done this in the past, but I'm not sure which one is better.

 

1) re-query the database. This gives you a fresh result that you can run your while loop on. Pro - it works. Con - requires an extra query to the database.

2) add an extra while loop after the first query, where you assign the results of the query to an array. Then, in your while loop, use that array instead of the query results. Pro - it works. Con - php intensive. Could take a long time for large result sets.

 

I'll be honest, I don't know which one is better, nor do I know if there is a better way than either of those. But they will both work.

 

 

[KrisNz]

that would be mysql_data_seek() you're looking for. But in your query you use $ordersid but the variable you initialize at the start is $orderid. Are you sure that's working?

[haku]

that would be mysql_data_seek() you're looking for.

 

Nice! I didn't know that one, but I will definitely be using it in the future.

[shayster]

Can you please give me an example of how to use the mysql_data_seek()? I tried to look it up and figure out how to use it by I am fairly new to this. Thanks again.

[haku]

It works the same as reset(). But reset() is for arrays, mysql_data_seek() is for query results.

 

mysql_data_seek($result,0) // 0 is the row you want the pointer to be set to

 

I believe that should work.

[shayster]

You guys were great. Thanks for the help!!!