[SOLVED] Detect failed select

[Jaguar]

I want to insert a new mime type if the mime type does not exist or I want to get the existing mime type ID. I know this is a common problem, but I can't figure it out. I've tried using mysql_num_rows, but it causes an error.

 

$mime_type = mysql_escape_string($_FILES['file']['type']);

$result = mysql_query("SELECT id, mime_type FROM mime_types WHERE mime_type = $mime_type");

if( !$result )
{	
  mysql_query("INSERT INTO mime_types (id, mime_type) VALUES ('', '$mime_type')");
  $mime_type_id = mysql_insert_id();
}
else
{
  $row = mysql_fetch_assoc($result);
  $mime_type_id = $row['id'];
}

[trq]

<?php

  $mime_type = mysql_escape_string($_FILES['file']['type']);
  if ($result = mysql_query("SELECT id, mime_type FROM mime_types WHERE mime_type = $mime_type")) {
    if (!mysql_num_rows($result)) {
      mysql_query("INSERT INTO mime_types (id, mime_type) VALUES ('', '$mime_type')");
      $mime_type_id = mysql_insert_id();
    } else {
      $row = mysql_fetch_assoc($result);
      $mime_type_id = $row['id'];
    }
  }

?>

[Jaguar]

That didn't work.

[trq]

Won't this always evalute true?

 

Not if the query fails.

[Jaguar]

Ok I figured a way. I think the problem was that I forgot quotes around WHERE mime_type = '$mime_type'

 

$result = mysql_query("SELECT id, mime_type FROM mime_types WHERE mime_type = '$mime_type' LIMIT 1");

if( mysql_num_rows($result) === 1 )
{
  $row = mysql_fetch_assoc($result);
  $thumbnail_mime_type_id = $row['id'];
}
else
{
  mysql_query("INSERT INTO mime_types (id, mime_type) VALUES ('', '$mime_type')");
  $mime_type_id = mysql_insert_id();
}

[Wildbug]

You should be checking for errors, especially while debugging.  In this way you can detect and echo problems.

 

<?php

$query = "SELECT id, mime_type FROM mime_types WHERE mime_type = '$mime_type' LIMIT 1";
$result = mysql_query($query);
// Check for errors here and display any plus the query if there are:
if (mysql_errno()) die 'Error: ',mysql_error(),"<br>\n$query";

// I like mysql_result() for returning a single value; it saves a step or two:
if ($result) $thumbnail_mime_type_id = mysql_result($result,0);
else
{
// If your id column is AUTO_INCREMENT, you don't need to specify an id non-value
  mysql_query("INSERT INTO mime_types (mime_type) VALUES ('$mime_type')");
  $mime_type_id = mysql_insert_id();
}

?>