[RESOLVED] PHP5 mysqli question

[wwwfilmfilercom]

Hi

I'm trying to get the hang of mysqli since I've been using PEAR but my new free host won't support them. Can someone just give me a quick rundown of howw to add, find, update and delete items please?

I have a table 'user' with the following fields: userid, username, password, ash, level, about, email.

I'm trying to register a user with the following code:

$query="INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('sssiss', $username, $pw, "0", 1, $aboutme, $email); 
$stmt->execute();

But I'm getting the following errors:

All data must be fetched before a new statement prepare takes place in 

and

Call to a member function bind_param() on a non-object 

Any help please?

[penagate]

The second error occurs if the statement cannot be prepared for whatever reason. You should use a check before binding parameters.

$query="INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)";
if (($stmt = $mysqli->prepare($query)) === false)
  exit($mysqli->error());  
else
{
  // etc.
} 

[penagate]

The first error occurs if you do not call $stmt->fetch() after executing a query that returns a result set.

[wwwfilmfilercom]

Thanks for that - it's uncovered the following problem:

/*CHECK USERNAME EXISTS*/
if ($flag==0) {
$query="SELECT username FROM user WHERE username=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $username); 
$stmt->execute(); 
echo $stmt->num_rows;
if ($stmt->num_rows<1) {
$flag=1;
$msg="This user does not exist";
} else {
$row = $stmt->fetch_assoc();
$u=$row['username'];
}
$stmt->close;
} 

My database Does have a user on it. However, searching for that user brings no results. The $echo results 0!

How can I correct this?

[penagate]

The num_rows field is not populated unless you call the store_result method. Until then, none of the result set is buffered.

if ($flag == 0)
{
  $stmt = $mysqli->prepare('SELECT 1 FROM user u WHERE u.username=?');
  $stmt->bind_param('s', $username);

  $stmt->execute();
  $stmt->store_result();

  if ($stmt->num_rows == 1)
    $u = $username;
  else
  {
    $flag = 1;
    $msg = 'This user does not exist';
  }

  $stmt->close();
} 

As an aside, you really should use more descriptive variable names than just 'flag'. Trust me, you'll come back to this in a few weeks/months/years and wonder what the heck you were thinking when you wrote it. Happens to me every time.

[wwwfilmfilercom]

I thought I had the hang of PHP/mysql but I've been using PEAR MDB2 so mysqli notation has confused me again.

I've tried to find good links to learn the basics of selecting, inserting, updating and removing data from a table but haven't found anything.

Do you know of any good links for this information OR Penagate could you show the basics? I'm sure it will help many others in the future.

Thanks!

- also I note the remark about descriptive name tags, cheers

[penagate]

Select one

if (($st = $db->prepare(
  'select distinct username, location from user where userid=?'
)) !== false)
{
  $user = new User();

  $st->bind_param('i', $userid);
  $st->bind_result($user->name, $user->location);

  if (!$st->execute())
    throw new DatabaseException($st->error);
  
  $st->fetch();

  $st->close();
  return $st->num_rows == 1;
}
else
  throw new DatabaseException($st->error); 

Select multiple

if (($st = $db->prepare(
  'select username, location from user'
)) !== false)
{
  $users = array();

  $st->bind_result($username, $location);
  
  if (!$st->execute())
    throw new DatabaseException($st->error);

  while ($st->fetch())
  {
    $user = new User();
    $user->name = $username;
    $user->location = $location;
    $users[] = $user;
  }

  $st->close();
  return $users;
}
else
  throw new DatabaseException($st->error); 

Insert

if (($st = $db->prepare(
  'insert into user (username, location) values (?,?)'
)) !== false)
{
  $st->bind_param('ss', $user->name, $user->location);

  if (!$st->execute())
    throw new DatabaseException($st->error);

  $st->close();
  return $st->errno == 0;
}
else
  throw new DatabaseException($st->error); 

Update

if (($st = $db->prepare(
  'update user set username=?, location=?'
)) !== false)
{
  $st->bind_param('ss', $user->name, $user->location);

  if (!$st->execute())
    throw new DatabaseException($st->error);

  $st->close();
  return $st->errno == 0;
}
else
  throw new DatabaseException($st->error); 

Delete

if (($st = $db->prepare(
  'delete from user where userid=?'
)) !== false)
{
  $st->bind_param('i', $userid);

  if (!$st->execute())
    throw new DatabaseException($st->error);

  $st->close();
  return $st->errno == 0;
}
else
  throw new DatabaseException($st->error); 

[wwwfilmfilercom]

That's awesome! I can't say I understand it all but with some trial and error and testing it out I'm sure I'll get the hang of most of it! Many thanks! I'll leave this thread open for one more day to let you know how I get on with it...

[wwwfilmfilercom]

Hmmm, Penagate I've done some testing on the above and actually they haven't helped much. Basically they include other things such as 'User' class which I can't use.

Can you just advise the basics.

For instance, I keep getting errors like 'member function on non-member object' or something else.

I have a simple 'user' table - userid, username, password, hash, level, aboutme, email.

All I would like are the basic examples of how to use php5/mysqli as you've described up above. I mean with the 'select one' example, I only want to select one field 'username' which matches '$username' thats been entered in the form (to see if it exists). But in your example you've got 'select distinct username, location' etc...

Can you please just write the very basics to get the data, it doesn't need if-statements etc; just the simple parts.

Many thanks!!!

[penagate]

The User and the DatabaseException identifiers were just examples of usage. You do not need them.


To check if a record exists you only need select a constant value.

$st = $db->prepare('select 1 from user where fieldname=?');
$st->bind_param('s', $username);
$st->execute();
$st->store_result();
if ($st->num_rows > 0)
  # record exists
else
  # record does not exist 

[wwwfilmfilercom]

Thank you for that, I figured it out in the end..

Here's one thing which doesn't seem to work, I'm trying to update a field in my user table and I'm sure the SQL is correct because I've used it before. I'm not sure if the php is wrong, it seems correct as I've used already:

/* MAKE HASH SESSION */
srand(time());
$hash = (rand()%99);
$hash = md5($hash);
session_start(); 
$_SESSION['hash'] = $hash;
/*ADD HASH TO DB*/
$stmt = $mysqli->prepare('UPDATE user SET hash=? WHERE userid=?');
$stmt->bind_param('si', $hash, $userid);
$stmt->execute();
echo "loggedin"; 

Later, when I printf the $_SESSION['hash'] and $dbhash (hash value from the db) I can see that $dbhash is empty. So the statement above is not working.

Can you see what I need to change?

[penagate]

Again, you really do need the checks that I showed you. You are making life difficult for yourself because you have no way of knowing what the error is.

[wwwfilmfilercom]

Ok I'll use the checks - you mean 'if (!$st->execute())
throw new DatabaseException($st->error);' I presume?

Well I had better get to work, I'll definitely take a look at this again later on.

Thanks for all the help so far!

[penagate]

And the one around the prepare() call as well.

I would say use exit() instead of throwing an exception, but that's bad in a production environment, and the less code you have to modify to go from development to production, the better. Instead, I suggest that you do create a DatabaseException class, and set a global exception handler. Then it is a snap to go from debugging mode error messages (with stack traces and so on if you like) to production mode 'friendly' messages — simply swap implementations of the exception class.

[wwwfilmfilercom]

You lost me on the last message Penagate, I haven't yet got to the things you described there but will look into them. As for now, I still don't see what the problem with my INSERT code is so if you can help please advise. Otherwise I'll just tinker away with it until I find an appropriate solution. Cheers!

[wwwfilmfilercom]

My code seems to work fine now, I think the problem was not executing and fetching statements higher up the chain - thanks for all your advice Penagate - first class!

[penagate]

I had this thread open the other day and totally forgot to post a reply. My apologies. I'm glad that you sorted the problem out.