Avatar of ambientsbs
ambientsbs
 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!
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
themrrobert

8/22/2022 - Mon
SOLUTION
themrrobert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
ramrom

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.