PHP - Store a SELECT Query and use it inside a INSERT query

Question

Hi i'm having trouble using the data from a select query inside a insert query.

This is my php code -

   <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbName = "arestaurant";


    $ID = $_GET["Id"];
    $QUANTITY = $_GET["Quantity"];
    $TABLE = $_GET["Table"];

    //Make connection
    $conn = new mysqli($servername,$username,$password,$dbName);
    // check connection
    if(!$conn) {
        die("Connection Failed. ".mysqli_connect_error());
    }

    $sql = "INSERT INTO tableorders (tableNumber,isPaid)
            VALUES ('".$TABLE."','0')";
    $result = mysqli_query($conn,$sql);



    $y = "SELECT orderId from tableorders WHERE tableNumber='$TABLE' ORDER by orderDate DESC limit 1 offset 0 ";
    $resulty = mysqli_query($conn,$y);

    if ($resulty !== false) {
    $value = mysqli_fetch_field($resulty);
    $orderid = $value['orderId']; < --  error


}

    $sqlquery = "INSERT INTO orderitems (orderId, productId, quantity)
            VALUES ('".$orderid."','".$ID."','".$QUANTITY."')";
    $result2 = mysqli_query($conn,$sqlquery);


?>

but im getting -

Fatal error: Cannot use object of type stdClass as array on line 30.

I have several ways in storing it and then using it again, but i can seem to find the solution.

please help?

Your code is vulnerable to [SQL Injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) attacks. [Use prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — tyteen4a03, Nov 13 '17 at 17:49
You need to first **Debug your own code** before asking a question. Lookup that error, look at that line number, figure it out. — IncredibleHat, Nov 13 '17 at 17:51
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Nov 13 '17 at 17:55
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Nov 13 '17 at 17:55
Honestly iv looked up every thread, i found the layout of how its suppose to look. I wouldn't have posted if i wasn't desperate. The insert works fine when i declare the $value, but its says that it can't convert the object into a string, so then i did a var_dump, but displays all characters instead the ID i need. — Joey, Nov 13 '17 at 17:55
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Nov 13 '17 at 17:55
Do try and get out of the habit of cluttering up your code with needless things like `!== false`. Many functions are designed to return values that evaluate as logically true or false so that's redundant. — tadman, Nov 13 '17 at 17:56
im using $_GET because its a quick way to test the php if its working. btw the database is hosted locally. — Joey, Nov 13 '17 at 17:57
If you're really struggling here because you're still learning about PHP and databases it might be worth checking out tools like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/master/eloquent) which can help you work with databases without getting too mired in the details. — tadman, Nov 13 '17 at 17:57
again used !== false from an example i found here on STACKOVERFLOW — Joey, Nov 13 '17 at 17:58
@Joey The problem is not using `$_GET`, but that this code is full of holes and will never work properly except under a narrow set of circumstances. If you do it by the book, correctly, not only will you have fewer problems to debug, but you'll be protected from unexpected and possibly hostile user input. There's no reason to just slap it together like this and expect it to work. — tadman, Nov 13 '17 at 17:58
@Joey Much as we try, not everything on Stack Overflow is going to be high quality. Whenever using a function, check to see what values it can return. If they're already boolean, a test like that is redundant and can even cause bugs. Sometimes these functions return a logically false but not *literal* false value on failure. Be careful, but don't be paranoid. A guide like [PHP the Right Way](http://www.phptherightway.com) can help with general advice like this. — tadman, Nov 13 '17 at 17:59
@Joey `VALUES ('".$orderid."','".$ID."','".$QUANTITY."')` is a textbook example of slapping things together, here using concatenation. It's also worrying that you have a mix of lower-case and all upper-case variables, as that's really unconventional. Take a more systematic approach, work from good examples [as given in the documentation](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and keep your code as clean as possible. Where there's mess there's usually bugs. — tadman, Nov 13 '17 at 18:01
im still a beginner and working hard to learn, so i do appreciate your advice, but im still sinking in the same puddle — Joey, Nov 13 '17 at 18:02
could you tell the 'basics' to where i can find and apply a SELECT query inside a INSERT query then? — Joey, Nov 13 '17 at 18:03
Use last_insert_id on `$result` rather than a second select.http://php.net/manual/en/mysqli.insert-id.php — chris85, Nov 13 '17 at 18:06
@Joey I feel for you. This can be a lot to take in, and unless you have a few good reference books that are current, or someone who can act as an advisor or mentor you might be in for quite a trip. That's why I strongly recommend those unfamiliar with all of the fundamentals start with something coherent, cohesive and full-featured like [Laravel](https://laravel.com) where there's a well-defined, documented method for performing common things, and heaps of community support and [training material](http://laracasts.com) specific to it. — tadman, Nov 13 '17 at 18:10
Welcome to Stack Overflow. This community really hates SQL injection, and makes that hatred clear in comments to questions showing vulnerable code. Why? Because cybercreeps often pwn vulnerable web sites *in less than a day.* In other news, perhaps you could tell us which line of your code is Line 30, where php threw the error. Please [edit] your question. — O. Jones, Nov 13 '17 at 18:14
simple, i am trying to get an ID from a table, thats why there is a SELECT statement, and then push that ID into an INSERT query, therefore storing it into another table — Joey, Nov 13 '17 at 18:16

acuedd · Answer 1 · 2017-11-13T18:48:38.280

0

This error its because the function mysqli_fetch_field return a object, you maybe wants read http://php.net/manual/es/mysqli-result.fetch-field.php and this function return info of each column.

change this:

    $value = mysqli_fetch_field($resulty);
    $orderid = $value->orderId;

Maybe you need use: fetch_assoc

http://php.net/manual/es/mysqli-result.fetch-assoc.php

good luck

edited Nov 13 '17 at 18:48

answered Nov 13 '17 at 18:44

acuedd

31
3

i got Undefined property: stdClass::$orderId on this line – Joey Nov 13 '17 at 18:48
$orderid = $value->orderId; – Joey Nov 13 '17 at 18:48
do i have to do it in a while loop? – Joey Nov 13 '17 at 18:49
Because mysqli_fetch_field return info of each column, maybe you need use fetch_assoc. – acuedd Nov 13 '17 at 18:49
I just changed my answer, with fetch assoc you can get data of column, in this case orderId – acuedd Nov 13 '17 at 18:51
hey i tried both, but with assoc i get this Parse error: syntax error, unexpected '$orderid' – Joey Nov 13 '17 at 19:00

score 0 · Answer 2 · answered Nov 13 '17 at 18:53

0

If you wants get data of column please use fetch assoc like this:

$y = "SELECT orderId from tableorders WHERE tableNumber='$TABLE' ORDER by orderDate DESC limit 1 offset 0 ";
$resulty = mysqli_query($conn,$y);

if ($resulty !== false) {
    $fila = $resultado->fetch_assoc()
    $orderid = $fila['orderId'];
}

answered Nov 13 '17 at 18:53

acuedd

31
3

what i dont get is, why is it unexpected when im assigning a value to it – Joey Nov 13 '17 at 19:00

Babak Bandpey · Answer 3 · 2017-11-14T09:13:17.767

The best solution is this:

https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

You don't need to pull the data out to insert it back. Let the MySQL do it for you.

This is my take on the combined insert-select statement. Would you try it?

$sqlquery = "INSERT INTO orderitems (orderId, productId, quantity)
SELECT orderId, $ID, $QUANTITY from tableorders WHERE tableNumber='$TABLE' ORDER by orderDate DESC limit 1";

I drop the offset because I didn't see the purpose of it.

Here is an example:

Imagine that there is table called tasks with many rows and we want to select an id value from it and insert it into this test table together with some external values.

mysql> create table test (id int, order_id int, product_id int);
Query OK, 0 rows affected (0.09 sec)

mysql> describe test;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | YES  |     | NULL    |       |
| order_id   | int(11) | YES  |     | NULL    |       |
| product_id | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into test select id, 5, 6 from tasks order by id asc limit 1;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+----------+------------+
| id   | order_id | product_id |
+------+----------+------------+
|    1 |        5 |          6 |
+------+----------+------------+
1 row in set (0.00 sec)

mysql> insert into test(id, order_id, product_id) select id, 5, 6 from tasks order by id asc limit 1;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+----------+------------+
| id   | order_id | product_id |
+------+----------+------------+
|    1 |        5 |          6 |
|    1 |        5 |          6 |
+------+----------+------------+
2 rows in set (0.00 sec)

mysql>

I hope that it shed some lights on the possibilities of the insert-select method

yes of course i agree, i did my research, but how can apply this method to php sql, that is mysql — Joey, Nov 13 '17 at 19:34

PHP - Store a SELECT Query and use it inside a INSERT query

3 Answers3