PHP array iteration in loop and put fetched results in a table with MySQL

Question

I am a beginner and read quite many threads here before posting as whatever i tried, could not do what i need to do.. so here's what I do:
1. Run a mysql query to DB by sorting in ASC and DESC mode and get 20 records of each type, I want to take 3 features: term, termid and currentRank.
2. Then I fetch these with mysqli_fetch_array
3. Then I need these 20 results to be stored in a temporary table, I suppose with some sort of loop.

So far i was able to iterate over the values and print them, but I am having trouble with running a query within the While loop to input the current values in the temporary table. Here my PHP for this part of the application:

<?php
$conn=mysqli_connect("localhost","root");
$db_select=mysqli_select_db($conn,"irdb");

//select a random logo from db
$query = mysqli_query($conn,"SELECT logo, companyid FROM company ORDER BY RAND() LIMIT 1");   

$row = mysqli_fetch_array($query);
$image =  mysqli_real_escape_string($conn,$row[0]); 
$compID = mysqli_real_escape_string($conn,$row[1]);

// create new term temporary table newtermlist
$newtermlist = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS irdb.newtermlist (
            `newtermid` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `origtermid` INT(10),
            `companyid` INT(20),
            `term` VARCHAR(50),
            `currentRank` decimal(50,0))");

// sort terms for the random company by highest ranked values
$highestRankTerms = mysqli_query($conn, "SELECT term, currentRank, termid FROM ranks_test 
WHERE companyID='$compID' 
ORDER BY currentRank DESC 
Limit 20");

$highestRow = mysqli_fetch_array($highestRankTerms);        
$highestTerm =  mysqli_real_escape_string($conn,$highestRow[0]); 
$highestCurrentRank = mysqli_real_escape_string($conn,$highestRow[1]);
$highestTermID = mysqli_real_escape_string($conn,$highestRow[2]);

while ($row = mysqli_fetch_array($highestRankTerms,MYSQL_ASSOC)){
      printf("<br> term: %s | currentRank: %s | termid: %s", $row["term"], $row["currentRank"], $row["termid"]);
      // I need to RUN the UPDATE QUERY below:
      // $insertInNewtermlist = mysqli_query($conn,"INSERT INTO `irdb`.`newtermlist` VALUES ('','$compID','$highestTerm','$highestCurrentRank')");
}

?>

When I print I get the following:

So it works partially.. The strange thing is that sometimes when I try different things, it gives me NO error, but no record is present in the table or at most two terms out of 20.

I would really appreciate help on it, cause i tried everything that I am capable of, but considering the fact that I am self-studying, I probably do not know something that might help.

Thanks, Ani

The code you show does not produce the output you show. Please make sure the code and output MATCH — RiggsFolly
– RiggsFolly, Commented Apr 2, 2016 at 19:21
@RiggsFolly Hello. That's probably because I purposely commented out the print statement in the while loop. I put it in comment as it is just for testing. I uncommented it now. And put the query i need to run in the While loop inside it, commented as it does not work. — Pixie
– Pixie, Commented Apr 2, 2016 at 19:28

Alex F. · Accepted Answer · 2016-04-02 20:31:01Z

Add error checking and result checking to your code:

$query_text = "SELECT logo, companyid FROM company ORDER BY RAND() LIMIT 1";
if ($query = mysqli_query($conn,$query_text)) {

     // If no errors occured then check that there are rows in the result:
     if ($row = mysqli_fetch_array($query)) {
        // We have at least one row then go on
        .......
     }
     else {
         echo 'No rows to fetch!';
     }
     // Don't forget to free results if you do not need them later
     mysqli_free_result($query);
}
else {
   echo 'Error in your query :'.$query_text;
}

Put simmilar checks in your code everywhere where you call mysqli_query and mysqli_fetch_array, musqli_fetch_assoc. Now you will be sure the queries are correct and results contain some data to handle. Otherwise you will get error messages to your output.

Pixie · Accepted Answer · 2016-04-03 16:27:15Z

**** SOLVED ****

So the thing was that I did not see the error message and had a wrong number of columns to insert into, instead of inserting in 5 columns, I was trying to insert in 4. So here's what I added in the while loop:

while ($row = mysqli_fetch_array($lowestRankTerms,MYSQL_ASSOC)){
    $insertInNewtermlistLOW = mysqli_query($conn,"INSERT INTO `irdb`.`newtermlist` (origtermid,companyid,term,currentRank) VALUES ('".$row["termid"]."','$compID','".$row["term"]."','".$row["currentRank"]."')");

    if(!$insertInNewtermlistLOW) {
        die("error in the insert Query above: " .mysqli_error($conn));
    }
}

Lesson to learn: ALWAYS MAKE SURE TO TEST THE QUERY AND OUTPUT THE ERROR
As soon as I saw the error I knew that to do..

Collectives™ on Stack Overflow

PHP array iteration in loop and put fetched results in a table with MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related