up vote 0 down vote favorite



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:

enter image description here

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

share|improve this question
    
The code you show does not produce the output you show. Please make sure the code and output MATCH – RiggsFolly Apr 2 '16 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. – Ani D. Apr 2 '16 at 19:28
up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote accepted

**** 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..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.