PHP MySQL - Function

Question

i wrote a PHP Function but it does nothing at a specific point.. im new to php and my english is bad, sorry for that.

<?php
function SQLwriteRecent($id, $title, $link) {
    $con=mysqli_connect("localhost","","","");
    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $count = mysqli_query($con,"SELECT count FROM recent WHERE sc_stream='$id'");

   if(!isset($count)) {
        try {
            mysqli_query($con,"INSERT INTO recent (title, link, sc_stream, count) VALUES ('$title', '$link', '$id',$count)");
            mysqli_close($con);
            return 1;
        } catch(Exception $e) {
            return 0;
        }
    } else {
        try {
            // ------ SHOW HERE!!!! ------------ //
            mysqli_query($con,"UPDATE recent SET count=$count WHERE sc_stream='$id'");
            mysqli_close($con);
            return 2;
        } catch(Exception $e) {
            return 0;            
        }       
    }


}
?>

the code runs every time until a specific point (i marked it in the code with // ------ SHOW HERE!!!! ------------ //)

in the sql table, currently there is no entry. so i should create a new row

whats wrong with that code?! :(

As an aside, you should read up on avoid SQL Injection - See this canonical question here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Rob Baillie, Aug 01 '14 at 08:53
Try adding an echo at the end of the function - do you get any output? I ask since you set `$count` (`$count = ...`) and then check `!isset($count)` - in this situation `isset` should always return true? (hence the `else` portion should execute. — Rob Baillie, Aug 01 '14 at 08:54
Also, do you have error reporting switched on (including notices). Are you getting anything in your error logs. What is the output of the script currently? — Rob Baillie, Aug 01 '14 at 08:56
@RobBaillie: nope, get no output at the end.. the last output i get with die(); is where i marked the code. how can i enable error reporting for that? — Mr. 0x50, Aug 01 '14 at 09:01
Your code doesn't show a `die`. Though, of course, if you have a die in your code you won't get any output after that point. Reviewing the answers below it looks like people are setting you on the right path (you'll need to combine the answers from pretty much everyone to get there), but if you'd like to help yourself to find the answer for yourself... Try `var_dump` ing $count to see what it contains before you `if(!isset($count))`. — Rob Baillie, Aug 01 '14 at 09:05
Regarding setting your error reporting level. You can do this with `error_reporting(E_ALL);` or `error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);`, depending on your PHP version (see here: http://php.net/manual/en/function.error-reporting.php) — Rob Baillie, Aug 01 '14 at 09:07

lshas · Answer 1 · 2014-08-01T08:59:10.883

2

Your script wont insert a new row, because you have defined $count, it is a mysqli_result object. You have to check if there is a row, something you could do like this;

Instead of

if(!isset($count))

use

if(mysqli_num_rows($count) == 0)

edited Aug 01 '14 at 08:59

answered Aug 01 '14 at 08:54

lshas

1,691
1
19
39

Sal00m · Answer 2 · 2014-08-01T09:06:21.140

2

Some explanation:

You have this in your code:

if(!isset($count)) {

This checks that your variable has been set, nor is empty, false, or 0. This condition ALWAYS return true because the variable is setted in line before, use mysqli_nuw_rows instead

edited Aug 01 '14 at 09:06

answered Aug 01 '14 at 08:58

Sal00m

2,938
3
22
33

score 0 · Answer 3 · answered Aug 01 '14 at 08:53

0

You did a mistake here, query returns array try this

mysqli_query($con,"UPDATE recent SET count=$count[0]['count'] WHERE sc_stream='$id'");

answered Aug 01 '14 at 08:53

SagarPPanchal

9,839
6
34
62

score 0 · Answer 4 · edited Aug 01 '14 at 09:00

0

You have set:

count=$count

but

$count = mysqli_query($con,"SELECT count FROM recent WHERE sc_stream='$id'");

Specify a proper value for count not a resource

edited Aug 01 '14 at 09:00

Rob Baillie

3,436
2
20
34

answered Aug 01 '14 at 08:54

Zaid Kajee

712
4
9
22

score 0 · Answer 5 · answered Aug 01 '14 at 09:14

to retrieve the actual result of the query you have to do something like

if ( $result = $con->query($sql)){ //perform the query
    if ($result->num_rows == 1){
        if ($row = $result->fetch_assoc()){
            $count = $row['count'];
        } 
        else{
          echo "couldn't fetch result row";
    }
    else {
        echo "expected one result row, got ".$result->num_rows;
    }
}
else {
    echo "query failed:".$sql;
    echo $con->errno.' '.$con->error;
}

// if you have more than one result row

if ( $result = $con->query($sql))
  while ($row = $result->fetch_assoc()){ //loop through the result(s)
     $count = $row['count']
  }

// procedural style

if ( $result = mysqli_query($con,$sql))
   while($row = mysqli_fetch_assoc($result)){

score 0 · Accepted Answer · answered Aug 01 '14 at 09:18

Combining what other people have said, and looking at the logic of what you're doing, it looks like you have a few fundamental issues:

I've tweaked some variable names to make it clearer what you're getting an peppered the code with comments that describe the issues.

I've ignored the SQL injection issues.

<?php

function SQLwriteRecent($id, $title, $link) {

    $con=mysqli_connect("localhost","","","");

    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $countQuery = mysqli_query($con,"SELECT count FROM recent WHERE sc_stream='$id'");

    $numberOfRowsReturnedByQuery = mysqli_num_rows($count);

    if ( $numberOfRowsReturnedByQuery > 0 ) {
        $valueOfCountInQuery = $countQuery [0]['count'];
    }

    if( $numberOfRowsReturnedByQuery == 0) {
        try {

            // In this situation it looks like you want to set up a value in "recent" - I.E. you didn't have a record.
            // But think about it for a second - if you had no record in "recent" then how could "$valueOfCountInQuery" possibly be set?

            mysqli_query($con,"INSERT INTO recent (title, link, sc_stream, count) VALUES ('$title', '$link', '$id',$valueOfCountInQuery )"); // makes no sense to use "$valueOfCountInQuery" - maybe you mean "0" (zero)
            mysqli_close($con);
            return 1;
        } catch(Exception $e) {
            return 0;
        }
    } else {
        try {

            // In this situation it looks like you want to update the value in "recent" - I.E. you DID have a record and you want to change it.
            // But think about it for a second - the value of "$valueOfCountInQuery" is the value that you got from "count" on "recent".  You are setting it to the same value that's already in there!

            // ------ SHOW HERE!!!! ------------ //
            mysqli_query($con,"UPDATE recent SET count=$valueOfCountInQuery WHERE sc_stream='$id'"); // redundant

            mysqli_close($con);
            return 2;
        } catch(Exception $e) {
            return 0;            
        }       
    }
}
?>

Works almost! But there is an error in if ( $numberOfRowsReturnedByQuery > 0 ) { $valueOfCountInQuery = $countQuery [0]['count']; } Fatal error: Cannot use object of type mysqli_result as array — Mr. 0x50, Aug 01 '14 at 09:56
solved added while($row = mysqli_fetch_array($countQuery)) { $valueOfCountInQuery = $row['count']; } — Mr. 0x50, Aug 01 '14 at 10:29
Oops. Really should run my examples before I post them. Glad to be of help. — Rob Baillie, Aug 01 '14 at 13:25

PHP MySQL - Function

6 Answers6

Linked