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: stackoverflow.com/questions/60174/… — Rob Baillie
– Rob Baillie, Commented Aug 1, 2014 at 8: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
– Rob Baillie, Commented Aug 1, 2014 at 8: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
– Rob Baillie, Commented Aug 1, 2014 at 8:56

lshas · Accepted Answer · 2014-08-01 08:59:10Z

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 1, 2014 at 8:59

answered Aug 1, 2014 at 8:54

lshas

1,7211 gold badge20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sal00m · Accepted Answer · 2014-08-01 09:06:21Z

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 1, 2014 at 9:06

answered Aug 1, 2014 at 8:58

Sal00m

2,9143 gold badges24 silver badges33 bronze badges

Comments

Rob Baillie · Accepted Answer · 2014-08-01 09:18:10Z

0

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;            
        }       
    }
}
?>

answered Aug 1, 2014 at 9:18

Rob Baillie

3,4702 gold badges24 silver badges35 bronze badges

3 Comments

Mr. 0x50 Over a year ago

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 Over a year ago

solved added while($row = mysqli_fetch_array($countQuery)) { $valueOfCountInQuery = $row['count']; }

Rob Baillie Over a year ago

Oops. Really should run my examples before I post them. Glad to be of help.

SagarPPanchal · Accepted Answer · 2014-08-01 08:53:18Z

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 1, 2014 at 8:53

SagarPPanchal

10.2k6 gold badges36 silver badges66 bronze badges

Comments

Rob Baillie · Accepted Answer · 2014-08-01 09:00:10Z

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 1, 2014 at 9:00

Rob Baillie

3,4702 gold badges24 silver badges35 bronze badges

answered Aug 1, 2014 at 8:54

Zaid Kajee

7124 gold badges9 silver badges23 bronze badges

Comments

Loopo · Accepted Answer · 2014-08-01 09:14:38Z

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)){

Collectives™ on Stack Overflow

PHP MySQL - Function

6 Answers 6

Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related