Comparing variable value with result of a query in PHP

Question

I'm trying to pull all the "totalsDate" values from a table "totals" and then either INSERT a new record or UPDATE an existing one based on a variable $date.

//getting all the dates from the totals table and assigning to row
    $sqlDate = "SELECT totalsDate FROM totals";
    $query = mysqli_query($dbCon, $sqlDate);
    //$row = mysqli_fetch_array($query);

    while($row = mysqli_fetch_array($query)){
      $rowDate = $row['totalsDate'];

       //if statement to either update the totals table or create a new record
      if($rowDate = $date){

      $sqlThree = "UPDATE totals SET lodgements = '$lodgementsAfter' WHERE branch_name = '$branchTest' AND totalsDate = '$date'";
      $query = mysqli_query($dbCon, $sqlThree);

      }

      else {
      $sqlFour = "INSERT INTO totals VALUES(NULL, '$branchTest', 0, '$amount', 0,  '$date')";
      $query = mysqli_query($dbCon, $sqlFour);
    }



    }

The update part works, however my else statement will never be executed and a new record cannot be entered. I also get this error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in

I'm a bit confused on using the mysqli_fetch_array and how to actually use the data?

Thanks for any help.

You could use `INSERT ... ON DUPLICATE KEY UPDATE` instead, btw. — Ja͢ck, Feb 18 '14 at 01:34

Ja͢ck · Answer 1 · 2014-02-18T01:58:01.390

One of the problem is that $query gets overwritten inside the loop; see here:

while($row = mysqli_fetch_array($query)){

    // ...
    $query = mysqli_query($dbCon, $sqlThree);
    // ...
}

You should either a) don't store the result of mysqli_query() at all, or b) choose a different variable name, e.g. $update_res = mysqli_query(...);.

Better yet, use a single query to do both:

INSERT INTO totals VALUES (NULL, :branch, 0, :amount, 0, :date) 
ON DUPLICATE KEY UPDATE lodgements = :lodgements

Just make sure the proper unique constraints are defined on the table.

bart2puck · Accepted Answer · 2014-02-18T01:43:22.910

0

your db query is not correct. You are not getting a good result back from your query. The error is saying it expects the result to be an array and its not. its returning a true of false.

read this mysqli_query

for more help.

do you have access to the db command line? what does the query return. Are you certain your db connection info is correct?

i found it.

 $rowDate = $date   should be $rowDate == $date or $rowDate === $date

your sql result is an array

$row[0] = "1st result"
$row[1] = "2nd result"
 ..etc

so theortically if you have more then 1 result, it should be doing multiple updates/inserts. are you seeing that? if its just one result then clearly 2014-02-13, 2014-02-26 are not the same.

 $sqlDate = "SELECT totalsDate FROM totals where totalsDate='" . $date . "'";

this will return results, with only records that have $date.

 $rowcount=mysqli_num_rows($query);
if ($rowcount) < 1))
{
   insert;
}
else
{
   update;
}

edited Feb 18 '14 at 01:43

answered Feb 18 '14 at 01:03

bart2puck

2,432
3
27
53

Yes everything is correct and as i said, my UPDATE part of the if statement executes 100% correct and works. it's just the else part of the statement that never executes – 93Alan Feb 18 '14 at 01:14
what is $date? i see $rowDate being defined, i dont see where $date is. – bart2puck Feb 18 '14 at 01:16
$date is just a variable which is set from the user input from a textbox. (date format) That fixes the problem of inserting a new record if the date doesnt exist however, it now doesnt update that, just inserts new one each time lol – 93Alan Feb 18 '14 at 01:21
what do you get if you print_r($row); – bart2puck Feb 18 '14 at 01:22
i get Array ( [totalsDate] => 2014-02-13 ) this is the first record in table – 93Alan Feb 18 '14 at 01:23
ok, so you have added the double equals and you are only getting the else condition? as a test before the if statement, do this. echo $rowDate . "," . $date; are they the same? – bart2puck Feb 18 '14 at 01:24
I'm getting 2014-02-13, 2014-02-26 13th being the first record in my table and 26th being $date (user input) – 93Alan Feb 18 '14 at 01:25
ok. let me edit answer. – bart2puck Feb 18 '14 at 01:26
Yes i understand that - how can i then compare my $date with all array elements in $row then? Just a for loop? and an if $row[i] == $date ~~~ ? – 93Alan Feb 18 '14 at 01:32
you are doing a loop already with the while statement. in your answer you gave me, you said [totalsDate] => 2014-02-13. 1 result, and is not equal to 2014-02-26. – bart2puck Feb 18 '14 at 01:34
so now im a bit confused, does your result have multiple entries like my $row example, or is it 1 result? – bart2puck Feb 18 '14 at 01:34
I know that, sorry. but in my table there are multiple dates, e.g. 2014-02-13, 2014-02-17, 2014-02-26 ... how can i move the array to next and then test the next element? for example, the 26th is the 3rd date in the table, if my $date is 2014-02-26, i'm wanting to check it against those e.g. 3 dates? and not just the first one – 93Alan Feb 18 '14 at 01:36
so in this example you will end up with 3 modifications to your db table. 2 updates, and 1 insert. are you seeing that? throw in an echo $sqlThree and $sqlFour right before their respective query calls to db. do you see 3 entries? – bart2puck Feb 18 '14 at 01:37
Yes i understand what it's doing now! :o How can i then alter my code to do what i want it do? Basically take $date, compare against all dates currently in the totals table, field totalsDate. "IF date exists then update ELSE INSERT new – 93Alan Feb 18 '14 at 01:39
I can't thank you enough, this worked a treat!! I added an extra bit of syntax to the $sqlDate query as there are more than one "branch" $sqlDate = "SELECT totalsDate FROM totals WHERE totalsDate = '$date' AND branch_name = '$branchTest'"; Thank you very much! – 93Alan Feb 18 '14 at 01:55

Comparing variable value with result of a query in PHP

2 Answers2