Creating multiple SQL Data from For-loop

sql for loop select
while loop in sql server stored procedure
for loop in sql query example
sql while loop counter
sql loop through select results
sql while loop through table rows
sql cursor loop
for loop in sql server stored procedure

I am trying to add random x and y values to a MySQL database but strangely it only seems to ever add one value. I was looking at many of the other posts of similar issues on Stackoverflow and it just seemed they did not have query within the loop was the common issue. I do have the query in the loop and am unsure why it is still not working.

Please see my code below:

<?php 
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myTable";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
for ($i = 0; $i <= 100; $i++){
    $x = rand(0,20);
    $y = rand(0,200);
    $sql = "INSERT INTO data (x, y)"
    $sql .= "VALUES ($x, $y);";
    //mysql_query($sql);

    if ($conn->multi_query($sql) === TRUE) {
        echo "New records created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

$conn->close();
?>

I edited your for loop to look something like this, and it worked perfectly fine for me.

for ($i = 0; $i <= 100; $i++){
    $x = rand(0,20);
    $y = rand(0,200);
    $sql = "INSERT INTO data (x, y) VALUES ('$x', '$y')";
    $result = mysqli_query($conn, $sql);
    if($result)
    {
        echo "Successfully created record " . $i;
    } else
    {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    ob_start();
    ob_flush();
    flush();
    usleep(001000);
    ob_flush();
    flush();
}

it Delay's the loop by just a little like a fifth of a second in total. The reason I have delayed the iteration is because your database might have a limit on how many queries can be sent within a time frame. It works for me let me know if it works for you.

SQL While loop: Understanding While loops in SQL Server, The article explains how to use the SQL While loop in Microsoft SQL Server We will create one table i.e. Cars within the CarShop database. Select Sql Courses Based On Your Individual Skill Level, Budget, And Schedule.

Create the values array in the for loop, then use implode to build the query outside the loop.

    $servername = "localhost";
    $username = "root";
    $password = "root";
    $dbname = "myTable";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_errno) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $sql = "INSERT INTO data (x, y) VALUES ";
    $values = [];
    for ($i = 0; $i <= 100; $i++){
        $x = rand(0,20);
        $y = rand(0,200);
        $values[] = "($x, $y)";
    }

    $query = $sql . implode(",", $values) . ";";
    $r = $conn->query($query);

    if ($r) {
      echo "New records created successfully";
    } else {
      echo "Error: " . $query  . "<br>" . $conn->connect_error;
    }

    $conn->close();

SQL WHILE loop with simple examples, SQL WHILE loop provides us with the advantage to execute the SQL statement(s) loop. In the following example, we will read table data, row by row. Firstly we will create a sample table: Studio · Multiple methods for scheduling a SQL Server backup automatically · When to Use Temporary Tables vs. 4 Answers 4. Create function that parses incoming string (say "AABBCC") as a table of strings (in particular "AA", "BB", "CC"). Select IDs from your table and use CROSS APPLY the function with data as argument so you'll have as many rows as values contained in the current row's data.

You are just running a single query for each loop. No need for multi query

for ($i = 0; $i <= 100; $i++){
    $x = rand(0,20);
    $y = rand(0,200);
    $sql = "INSERT INTO data (x, y)"
    $sql .= "VALUES ($x, $y);";
    //mysql_query($sql);

    if (mysqli_query($conn,$sql)) {
        echo "New records created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

PL/SQL Cursor FOR LOOP Statement By Practical Examples, This tutorial introduces you the PL/SQL cursor FOR LOOP statement and show The cursor FOR LOOP implicitly creates its loop index as a record variable with one row at a time, Oracle Database fetches multiple rows at a time and allows  SQL stands for Structured Query Language. It is specifically designed to retrieve and manipulate data from relational databases. PL/SQL is a procedural extension of SQL and is well integrated with the latter. This language contains important programming features which are combined with SQL to create useful data driven applications. PL/SQL code can be called from …

6 Performing SQL Operations from PL/SQL, See also: LOOP Statements In a cursor FOR loop, PL/SQL creates a Process several queries in parallel by declaring and opening multiple cursors. For Loop within SQL. Actually I’m cheating here. There is no FOR loop in T-SQL currently, but you can simulate a FOR-loop using WHILE . Here is an example of using a “For loop” to split a string based on a separator. Note - This is not my work - the example is taken directly from SQLServerCentral.com

LOOP Statements, Database PL/SQL Language Reference. Contents A LOOP statement executes a sequence of statements multiple times. PL/SQL provides these loop  The Nested SQL While Loop is nothing but placing While Loop inside another While Loop. SQL Server Nested While loops are instrumental when you are working with multi-layered data. Because when we want to select the layered data, we have to use this SQL Nested While loops to extract the layered data. But please be careful while using it.

PHP Loops, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML. In the following chapters you will learn how to repeat code by using loops in PHP. Copyright 1999-2020 by Refsnes Data. All Rights Reserved. Using the "CREATE SCHEMA" command you can combine multiple DDL steps into a single statement. As a result, the failure of a single part causes all commands within the "CREATE SCHEMA" to be rolled back. CREATE SCHEMA is limited to creating tables, views and issuing grants.

Comments
  • If it's MySQL, then please don't tag SQL Server; they are not the same product (or even owned by the same company). Every DBMS is different, so it's important to know which one you are using. I've removed the tag for you.
  • are their any key constraints on x and y in your table ? If anyone of them is PK or Unique key, then your query may fail, because same random value may appear
  • @Madhur Bhaiya I do not believe I did but I do not need to have any constraints on those values is there a certain way I should set up these two variables. Thank you
  • you are using mysql multiquery but you should use mysqli_query since you are running a single query each time you do a new step of the for loop
  • That makes sense to me so when I ran the code and tried to check the data it gives an error " Unknown table status: TABLE_TYPE" right now I make they type as Collation
  • where does your db exists is it online at phpmyadmin or somewhere else? you might also try this link it just has that you might need to update your server version.
  • I am using UWAMP it is a phpmyadmin and is only hosted locally
  • ok let me see if I can find anything specific to UWAMP and "Unknown table status: TABLE_TYPE".
  • As far as I can find it has to do with either your db version, UWAMP version or The redestributable that you are using I would recommend going to uwamp's site and downloading the latest redestributable, and if that doesn't fix the problem try the latest UWAMP exe file, and install that and if that doesn't work then your DB's version needs to be changed you can look into that. Hope this helps. Just try those options.
  • I see your point but strangely even with this code the result is the same and only 1 data set is added to the table
  • Well that makes no sense, if you print $query you will see something like: INSERT INTO data (x, y) VALUES (2, 136),(16, 144),(19, 139),(17, 36),(19, 165),(16, 31); (but longer), If you execute this query it will insert all the rows at once. dev.mysql.com/doc/refman/8.0/en/insert.html
  • That's true about the single query I am still unsure as to why only one data point gets added instead of the iterated 100 values
  • Yes I have, the directory of my database is like so. Database is called "mytable" and the table is called "data" both x and y are type "int(11)"
  • and you do not get any error message from the output above?