PHP MySQLi issue with prepared statements causing error

Question

I am having an issue trying to get a php script to work for my website where I can add new items to my online store.

The code snippet I am having issues with is this:

$error = '';
$success = '';

if(isset($_POST['submit']))
{
    define("MAX_SIZE", "4096");

    $errors=0;

    $name = $_POST['name'];
    $price = $_POST['price'];
    $desc = filter_var($_POST['desc'], FILTER_SANITIZE_STRING);
    $image = $_FILES['image']['name'];
    $uploadedfile = $_FILES['image']['tmp_name'];

    if($image)
    {
        $filename = stripslashes($_FILES['image']['name']);
        $extension = getExtension($filename);
        $extension = strtolower($extension);
        if(($extension != "jpg") && ($extension != "jpeg") && ($extension != "png") && ($extension != "gif"))
        {
            $error .= '<p class="error">The file must be one of the following file types(jpg|jpeg|png|gif)</p>';
            $errors=1;
        }
        else
        {
            $size=filesize($_FILES['image']['tmp_name']);

            if($size > MAX_SIZE*1024)
            {
                $error .= '<p class="error">The file cannot exceed 4MB in size.</p>';
                $errors=1;
            }

            if($extension=="jpg" || $extension=="jpeg")
            {
                $uploadedfile = $_FILES['image']['tmp_name'];
                $src = imagecreatefromjpeg($uploadedfile);
            }
            else if($extension=="png")
            {
                $uploadedfile = $_FILES['image']['tmp_name'];
                $src = imagecreatefrompng($uploadedfile);
            }
            else
            {
                $src = imagecreatefromgif($uploadedfile);
            }

            list($width,$height)=getimagesize($uploadedfile);

            $newwidth=128;
            $newheight=128;
            $tmp=imagecreatetruecolor($newwidth,$newheight);

            imagecopyresampled($tmp,$src,0,0,0,0,$newwidth,$newheight,$width,$height);

            $filename = "../store/images/" . $_FILES['image']['name'];

            imagejpeg($tmp,$filename,100);

            $fname = $_FILES['image']['name'];

            imagedestroy($src);
            imagedestroy($tmp);
        }
    }

    if($name == '')
    {
        $error .= '<p class="error">You must enter a name for the product.</p>';
    }

    if(!preg_match('/^[a-zA-Z\s]+$/', $name))
    {
        $error .= '<p class="error">The product name can only contain letters and spaces.</p>';
    }

    if($_POST['type'] == 'KIT')
    {
        $type = 'KIT';
    }
    else if($_POST['type'] == 'VIP')
    {
        $type = 'VIP';
    }
    else if($_POST['type'] == 'OTHER')
    {
        $type = 'OTHER';
    }
    else
    {
        $error .= '<p class="error">You must select a valid type for the product.</p>';
    }

    if($price == '')
    {
        $error .= '<p class="error">You must enter a price for the product.</p>';
    }

    if(!preg_match('/^\d+(:?[.]\d{2})$/', $price))
    {
        $error .= '<p class="error">You must enter a valid price for the product.</p>';
    }

    if($desc == '')
    {
        $error .= '<p class="error">You must enter a description for the product.</p>';
    }

    $stmt = $db->prepare("SELECT * FROM products WHERE name = ?");
    if($stmt)
    {
        $stmt->bind_param('s',$name);
        $stmt->execute();

        if($stmt->num_rows > 0)
        {
            $error .= '<p class="error">This product already exists. Please choose a different name.</p>';
        }
    }
    else
    {
        $error .= '<p class="error">An error occurred at line 135. Please contact the site administrator.</p>';
    }

    if(empty($error) && !$errors)
    {
        $stmt = $db->prepare("INSERT INTO products (name, image, price, prod_desc, type) VALUES ( ?, ?, ?, ?, ? )");

        if($stmt)
        {
            $stmt->bind_param('sssss', $name, $fname, $price, $desc, $type);

            if($stmt->execute())
            {
                $success .= '<p class="success">Product added successfully.</p>';
            }
            else
            {
                $error .= '<p class="error">An error occured at line 158. Please contact the site administrator.</p>';
            }
        }
        else
        {
            $error .= '<p class="error">An error occured at line 154. Please contact the site administrator.</p>';
        }
    }
}

the section I am having an issue with is the if($stmt) block right after the if(empty($error) && !&errors) statement.

According to every syntax checker I've used for both the PHP itself, and the MySQL insert query, they all report no errors. But it keeps spitting out the error for line 154, and I can't figure out why. I've already verified the variables are being stored properly from the form by echoing them out, and I have also verified that the columns of the mysql table do exist, and are spelled correctly.

This is starting to drive me nuts, and when I tried to echo out the mysql error using $stmt->error or $db->error it came back blank, with no error whatsoever listed.

The only result I ever got was when I did mysqli_errno($db) which returned a 0.

Anyone know what is wrong with this code, or why it isn't working?

HTML Markup for the form:

<div id="form">
<form action="" method="post" enctype="multipart/form-data">
<table>
<th colspan="2">Add to Store</th>
<tr>
<td colspan="2">
<p>Please use the form below to add items to the store.</p>
<?php

if($error)
{
    echo $error;
}

if($success)
{
    echo $success;
}

?>
<hr>
<tr>
<td><label for="name">Product Name:</label></td>
<td><input type="text" name="name" id="name" value="<?php if(isset($name)) { echo $name; } else { echo ''; } ?>" /></td>
</tr>

<tr>
<td><label for="price">Product Price:</label></td>
<td><input type="text" name="price" id="price" size="6" maxlength="6" value="<?php if(isset($price)) { echo $price; } else { echo ''; } ?>" /></td>
</tr>

<tr>
<td><label for="image">Product Image:</label></td>
<td><input type="file" name="image" id="image" /><br /><small>File must be 128x128 pixels, and no larger than 4MB.</small></td>
</tr>

<tr>
<td><label for="type">Product Type:</label></td>
<td>
<select name="type">
<option value="VIP">VIP Package</option>
<option value="KIT">Donator Kit</option>
<option value="OTHER">Other</option>
</select>
</td>
</tr>

<tr>
<td colspan="2"><label for="desc">Product Description:</label></td>
</tr>
<tr>
<td colspan="2"><textarea name="desc" style="width: 500px; height:250px;"><?php if(isset($desc)) { echo $desc; } ?></textarea></td>
</tr>

<tr>
<td colspan="2"><input type="submit" name="submit" id="submit" value="Add Product" /></td>
</tr>

</table>
</form>
</div>

Answer 1

You never fetched any results, so num_rows will always be zero. There is a simple patch, use store_result():

$stmt->bind_param('s',$name);
$stmt->execute();
$stmt->store_result();

if($stmt->num_rows > 0)
{
    $error .= '<p class="error">This product already exists. Please choose a different name.</p>';
}

However, your code needs much more than that. You really should rewrite it and avoid nesting if statements so much. Use proper error reporting too.

Answer 2

So after much headache, I managed to find a workaround.

The source of the problem existed here:

$stmt = $db->prepare("SELECT * FROM products WHERE name = ?");
    if($stmt)
    {
        $stmt->bind_param('s',$name);
        $stmt->execute();

        if($stmt->num_rows > 0)
        {
            $error .= '<p class="error">This product already exists. Please choose a different name.</p>';
        }
    }
    else
    {
        $error .= '<p class="error">An error occurred at line 135. Please contact the site administrator.</p>';
    }

after changing this to the following:

$sql = "SELECT * FROM products WHERE name = '".$name."' LIMIT 1";
    $result = $db->query($sql);

    if(is_object($result) && $result->num_rows == 1)
    {
        $error .= '<p class="error">This product already exists. Please choose a different name.</p>';
    }

All is happy, and works without issue now. Still not sure why the original code didn't work, but it works now. Thanks for all the help.