mysqli_num_rows not working in MySQLi with PHP

Question

I would like to check if there already exists a record before inserting a new one. But it doesn't work so far, here is the script:

<?php
session_start();
$uname = $_SESSION['username'];
$friend = $_GET["newfriend"];

$db = new mysqli("localhost", "...", "....", "...");
if($db->connect_errno > 0){
    die("Unable to connect to database: " . $db->connect_error);
}

$checkexist = $db->query("SELECT * FROM friends WHERE (username_own='$uname', username='$friend')");

//create a prepared statement
$stmt = $db->prepare("INSERT INTO friends (`username_own`, `username`) VALUES (?,?)");

//bind the username and message
$stmt->bind_param('ss', $uname, $friend);

if ($checkexist->mysqli_num_rows == 0) {
  //run the query to insert the row
  $stmt->execute();
}

`WHERE (username_own='$uname', username='$friend')` err... that's wrong. It's either `AND` or `OR`. — Funk Forty Niner, Feb 19 '15 at 17:24
Why don't you just use `INSERT IGNORE`? If there's a duplicate, it will just skip the insert. — Barmar, Feb 19 '15 at 17:24
Do be careful to prepare any and all queries that involve user data. Right here you have code so dangerously bad it could trash your entire service. Properly prepare both of these queries. — tadman, Feb 19 '15 at 17:24
**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. — Andy Lester, Feb 19 '15 at 17:27
*"Every good answer gonna recieve a yummy cookie :)"* - I rather have a jam `$_SESSION` ♫ thank you. — Funk Forty Niner, Feb 19 '15 at 17:28
`$checkexist->mysqli_num_rows` is wrong. Use `$checkexist->num_rows` — worldofjr, Feb 19 '15 at 17:30

score 4 · Accepted Answer · edited Feb 19 '15 at 18:04

4

Try something like this:

<?php

/* Check if user exists */
$query = "SELECT count(1) FROM friends WHERE username_own=? AND username=?";

if($stmt = $db->prepare($query)){
  $stmt->bind_param('ss', $uname, $friend);
  $stmt->execute();
  $stmt->bind_result($count_rows);
  $stmt->fetch();
  $stmt->close();
}else die("Failed to prepare");

/* If user doesn't exists, insert */
if($count_row == 0){

    $query = "INSERT INTO friends (`username_own`, `username`) VALUES (?,?)";

    if($stmt = $db->prepare($query)){
      $stmt->bind_param('ss', $uname, $friend);
      $stmt->execute();
      $stmt->close();
    }else die("Failed to prepare!");
}

edited Feb 19 '15 at 18:04

Funk Forty Niner

74,450
15
68
141

answered Feb 19 '15 at 17:21

Arian Faurtosh

17,987
21
77
115

@Fred-ii- oops I meant to change that – Arian Faurtosh Feb 19 '15 at 17:26
Thanks for fixing it. Another thing though, it's hard to say whether OP wants to check for both or either. So it could also be an `OR`. But, we'll wait to see what OP has to say, *cheers* – Funk Forty Niner Feb 19 '15 at 17:27
@Arian There were some little mistakes in your script but now it works :) Thanks! For future readers, could you please delete this line: `$stmt->bind_param()` and add a semicolon here: `$stmt->close();` Here is the cookie 0 =) – mm ibm Feb 19 '15 at 17:50
@mmibm I took care of it, thanks for pointing that out. Can I have a cookie too? ;-) – Funk Forty Niner Feb 19 '15 at 18:04
1

@Fred -ii- Of course you can have one - Here is the cookie O – mm ibm Feb 19 '15 at 18:35
Thanks for the cookie, thanks @Fred-ii- for fixing that. – Arian Faurtosh Feb 19 '15 at 19:21

score 0 · Answer 2 · answered Feb 19 '15 at 17:18

0

Try this:

//create a prepared statement
$stmt = $db->prepare("INSERT INTO friends (`username_own`, `username`) VALUES (?,?)");

//bind the username and message
$stmt->bind_param('ss', $uname, $friend);

if ($checkexist->mysqli_num_rows == 0 || $checkexist->mysqli_num_rows <> 0) {
  //run the query to insert the row
  $stmt->execute();
}

answered Feb 19 '15 at 17:18

bcesars

1,016
1
17
36

You wrote `if ($checkexist->mysqli_num_rows == 0 || $checkexist->mysqli_num_rows <> 0)`. Why not just use `if(true)` as it would have the same result (of course, so would removing the if block). – worldofjr Feb 19 '15 at 17:37

score 0 · Answer 3 · answered Feb 19 '15 at 17:25

0

$checkexist->mysqli_num_rows is wrong. It's just

$checkexist->num_rows

or you can use

mysqli_num_rows($checkexist)

Hope this helps.

answered Feb 19 '15 at 17:25

worldofjr

3,868
8
37
49

score 0 · Answer 4 · answered Feb 20 '15 at 00:11

0

Replace most of your code with a simple INSERT IGNORE ... or INSERT ... ON DUPLICATE KEY UPDATE ....

The latter lets you change columns if the record already exists (based on any PRIMARY or UNIQUE key(s)).

answered Feb 20 '15 at 00:11

Rick James

135,179
13
127
222

mysqli_num_rows not working in MySQLi with PHP

4 Answers4