Using mysql_fetch_array in INSERT statement

Question

I am trying to use the result of one mysql query in another mysql query, but I'm obviously doing something wrong. This is what I have:

<?php
$result = mysql_query('SELECT panel_product_no 
        FROM panelProduct 
        WHERE length_mm = "' . ($_POST["p_length_mm"]) . '"
        AND width_mm = "' . ($_POST["p_width_mm"]) . '"
        AND veneer_type = "' . ($_POST["p_veneer"]) . '"
        AND lipping = "' . ($_POST["p_lipping"]) . '"');

$panel = mysql_fetch_array($result);
?>

And then I want to use that in this bit:

<?php
if(!empty($_POST[p_length_mm]) && !empty($_POST[p_width_mm]) && !empty($_POST[p_aperture]))
{
$sql3="INSERT INTO estimateDescribesPanelProduct (estimate_no, panel_product_no, quantity)

VALUES ('$_GET[estimate_no]','$panel','$_POST[p_quantity]')";

if (!mysql_query($sql3,$con))
  {
  die('Error: ' . mysql_error());
  }
}
?>

The query is basically working in that it is inserting the posted estimate_no and quantity into the DB, but not the correct panel_product_no (it just inserts '0'). How can I get it to insert the $result value?

P.S. I know that I should not be using mysql functions and I will not be in future, however I am so nearly finished with this project that at this point I am not in a position change.

You need to either loop through the result or use if condition. mysql_fetch_array returns a resource. Not results.. do this while($row=mysql_fetch_array($result)){ $my_variable=$row['length_mm'];} . Also us PDO or Mysqli, those mysql functions are garbage — Dmitry Makovetskiyd
– Dmitry Makovetskiyd, Commented Sep 3, 2012 at 17:50

JvdBerg · Accepted Answer · 2012-09-03 17:50:59Z

1

Your are basicly copying content from one table to another.

Wy not use the MySQL INSERT .. SELECT syntax?

answered Sep 3, 2012 at 17:50

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dsaa · Accepted Answer · 2012-09-03 18:18:26Z

0

as @Dmitry Makovetskiyd wrote, mysql_fetch_array() returns a resource, not manipulatable results.

For example:

$result = mysql_query('SELECT panel_product_no 
    FROM panelProduct 
    WHERE length_mm = "' . ($_POST["p_length_mm"]) . '"
    AND width_mm = "' . ($_POST["p_width_mm"]) . '"
    AND veneer_type = "' . ($_POST["p_veneer"]) . '"
    AND lipping = "' . ($_POST["p_lipping"]) . '"');

$resource = mysql_fetch_object($result);

You need to add in:

$panel = $resource->'panel_product_no';

You can then continue with your second query.

Note the change from mysql_fetch_array() to mysql_fetch_object() - as your query suggests you are only retrieving a singular value from the table (assuming there is only a singular panel with the specified length, width, veneer type and lipping), the object method will work fine.

answered Sep 3, 2012 at 18:18

dsaa

4872 silver badges20 bronze badges

3 Comments

tadman Over a year ago

NO. This is example is so completely reckless that if you put this on a production server, you could be fired. This is how companies get their databases published on the internet. Don't pretend there aren't severe SQL injection vulnerabilities here.

dsaa Over a year ago

@tadman I wasn't pretending - simply a case of I wasn't aware. I've read through the PHP manual for mysqli, I'm still unsure how this can prevent injection when obtaining a value inputted through a form. Perhaps you could describe how I would obtain a field's value and execute a statement - surely at some point I would need to use the $_POST/GET technique? I realise the mysqli approach is more secure when it comes to the execution, but how does this prevent injection?

tadman Over a year ago

The way to do this is using mysqli placeholders and bind_param. Your query should look like WHERE length_mm=? AND width_mm=? ... and then you substitute values when you bind. This is all documented on the bind_param page and even includes a few examples. That you're unaware of this is the fault of the PHP community in general, which tends to promote very bad habits, more than you personally.

Collectives™ on Stack Overflow

Using mysql_fetch_array in INSERT statement

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related