asked on

MYSQL Insert Values with Where condition

Hello experts,

I have a MYSQL query question that I think will be fairly simple to answer but I'm just not sure of the proper syntax to use. I'm looking to insert values into a table that has a UserID as the primary key, however, I don't want the values to be inserted if the UserID has more than 3 records associated with it. Basically, something like:

INSERT INTO AutosForSale (UserID, Year, Make, Model, Model_Description, Price, City, State, Zip, Mileage, Exterior_Color, Interior_Color, Description)
VALUES ($userid','$year','$make','$model','$modeldesc','$price','$city','$state','$zip','$mileage','$extColor','$intColor','$description')
WHERE (SELECT Count(*) FROM AutosForSale WHERE UserID = '$userid') < 3";

Open in new window

I'm using PHP so the $ values are inputs I'm getting from my user.

Can someone please recommend the proper way to do this?? My code posted above does not work correctly.....Thanks!

SOLUTION

themrrobert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ramrom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

themrrobert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

ramrom has pinpointed it and the code for such a check in PHP would depend on what kind of mysql interface you usually use.

With mysqli for example, it could look like this:

$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");

if ($result = $mysqli->query("SELECT Count(*) FROM AutosForSale WHERE UserID ='$userid'")) 
{
  if ($result->num_rows<3)
  { 
     $mysqli->query("INSERT ...")
  }
  else
  { 
    // inform user of the limit of records allowed
  }
}

Open in new window

Also take the advice from themrrobert to sanitize input. That could even start with the $userid, though I assume you determine this from the user logn, PHP session or something like that. But if it comes from db, is embedded in a hidden input type and comes back as $_POST variable in the first place, you can't be sure the user didn't automate a browser and changed that value.

Bye, Olaf.

ambientsbs

ASKER

I'm actually using this code from an iPhone app. Do I need to worry about sanitizing in that case?

Dave Baldwin

Yes you do because people who cause problems won't be connecting thru your app. They may use your app to find out where to post but then they will send data directly to the PHP page to try to break in.

themrrobert

Yes, any time you use user input YOU HAVE TO SANITIZE. This is because on a webserver, the iphone app isn't the only device that can access your page.

For example, a user can 'sniff' out the URL that the app uses, and then use a browser addon or a custom script to connect to the page and send malicious data.