How to make php script to run insert sql Query and then get information from the inserted rows ? I am a Beginner

Question

I have so far this script and I am aiming to run the sql insert query and fetch the results and echo some of them to appear in the web page. I want to have id and code and Period to appear in the output. Everytime, I run the script, it gives me the error message ' 0 results ' even though I checked through phpmyadmin that the query was successful and a new row was inserted as I hoped.

I copied some parts of this code from different forms, I doubt that my success key "if ($result->num_rows) {" with insert sql are not a match. Could someone help me with how to define correct success key for the insert query and help me to output the ID and CODE and PERIOD from the inserted data. Thank you very much

    <?php
$servername = "localhost";
$username = "myuser";
$password = "mypass";
$dbname = "xtream_iptvpro";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$randnum = rand(1111111111111111,9999999999999999);
$ipaddress = $_SERVER['REMOTE_ADDR'];
$sql = "INSERT INTO codes (id,code,period,userid,statut,prixcode,ip) VALUES (null,'$randnum',365,73,0,0,'$ipaddress')";
$result = $conn->query($sql);

if ($result->num_rows) {
    while($row = $result->fetch_assoc()) {
        echo $row['id']."  ".$row['code']."<br/>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

You must perform a SELECT query to get data from the database. An INSERT does not return anything. — Jay Blanchard
– Jay Blanchard, Commented Feb 24, 2020 at 3:26
Or use mysqli::$insert_id to get the autoincrement id value. — Shadow
– Shadow, Commented Feb 24, 2020 at 3:37

HoldOffHunger · Accepted Answer · 2020-02-24 23:15:50Z

1

You can remove the $result = setting, because an INSERT will not return the results of what records it added. You must do a SELECT for this. I'm also going to fix your code to use prepared statements. Change this part of your code to simply...

$sql = $conn->prepare('INSERT INTO codes (code,period,userid,statut,prixcode,ip) VALUES (?,?,?,?,?,?)');
$sql->bind_param('iiiiis', $randnum, 365, 73, 0, 0, $ipaddress);
$sql->execute();

And then just after this, try setting the result using mysql_insert_id() on the connection var and doing a select with a normal, prepared statement.

$resultid = $conn->insert_id;

$statement = $conn->prepare('SELECT * FROM codes WHERE id = ?');
$statement->bind_param('i', $resultid);
$statement->execute();
$result = $statement->get_result();

$result will now hold the results of the newest INSERT. Try doing print_r($result) and then adjusting your display-code afterwards to handle that data format.

edited Feb 24, 2020 at 23:15

answered Feb 24, 2020 at 21:11

HoldOffHunger

21.3k11 gold badges123 silver badges147 bronze badges

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

2 Comments

HoldOffHunger Over a year ago

@miken32: Good idea, coding without prepared statements just feels wrong, thanks!

Shadow Over a year ago

Why do you mix the procedural api with the oop one?

Collectives™ on Stack Overflow

How to make php script to run insert sql Query and then get information from the inserted rows ? I am a Beginner

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related