Jump to content



Photo

MySQL Statement Help


  • Please log in to reply
6 replies to this topic

#1 DeathLace

DeathLace

    Neowinian God!

  • Joined: 27-November 01
  • Location: Toronto, Canada

Posted 29 August 2014 - 14:53

Let's pretend the MySQL table is like so:

 

users:

id (auto increment primary key)

name (varchar[255])

inserted (datetime)

 

Basically I want to do a check to see if the table 'users' has less than 20 rows in it, and if so insert a new row.

 

I'm hoping to do this with one MySQL command.




#2 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 13
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 29 August 2014 - 14:56

you may be able to use

INSERT INTO TargetTable(Column1, Column2, ...)
    SELECT 'Value For Column 1', 'Value For Column 2', ...
      FROM Dual
     WHERE (SELECT COUNT(*) FROM TargetTable WHERE ...Some Condition...) < 20;

what Language are you using?

 

to get the number of rows you can use

SELECT COUNT(*) FROM TABLE_NAME;

It will be slow on large tables

 

Then your the lgauage your using to do the rest  so in normal 

SELECT COUNT(*) FROM TABLE_NAME

if count is below 20 then

add new row to table

else

do ntohing

End if


#3 Squirrelington

Squirrelington

    Squirrelies!

  • Tech Issues Solved: 2
  • Joined: 26-December 02
  • Location: Oshkosh, WI, USA
  • OS: Windows 8.1 64-bit Enterprise
  • Phone: Samsung Galaxy S5 (SM-G900T) - T-Mobile - 4.4.4

Posted 29 August 2014 - 15:06

it may be more efficient to do SELECT COUNT(0) FROM TABLE;

 

I know you can do that on MSSQL but maybe MSSQL/MYSQL optimizes it in the background, knowing you don't care about the columns in a COUNT() function.

 

Either way, Haggis is right, checking count on the table is the way to go.



#4 OP DeathLace

DeathLace

    Neowinian God!

  • Joined: 27-November 01
  • Location: Toronto, Canada

Posted 29 August 2014 - 15:07

Hey Haggis,

 

Thanks for your reply. I'm building this for a PHP website that's using MySQL.

 

The table won't be large, it'll only hold 50-100 records, it's for a contest. The reason I want to make it all in one MySQL command is so that if two users try to do it at the same time, only one will be successful. Instead of both doing a MySQL Count(*) and seeing it's below a number (let's say 20), and then both doing an INSERT, when only one should be able to.

 

I need to limit how many records are in this table, as it's for the prizes, and I just wanted to safeguard against it.  Currently I prepopulate the 50-100 rows in the table, and run an UPDATE command, but was hoping to make this more dynamic, as I know there has to be a better way.

 

So basically I was hoping I could have something like:

 

INSERT INTO targetTable (key, key, key) VALUES (value, value, value) WHERE (SELECT count(*) FROM targetTable) < 20

 

I tried that as an example, it didn't work though.



#5 S7un7

S7un7

    Neowinian

  • Joined: 07-October 04
  • Location: PA

Posted 29 August 2014 - 16:46

Try an if statement in the SQL. This is MSSQL:

 

if (select COUNT(*) from MyTable) < 20

   begin

         insert into TableX......

   end

 



#6 OP DeathLace

DeathLace

    Neowinian God!

  • Joined: 27-November 01
  • Location: Toronto, Canada

Posted 29 August 2014 - 17:56

Thanks for the suggestions guys... seems MySQL doesn't let you do an IF statement in a regular query, only in stored functions, so no luck.



#7 Brian M.

Brian M.

    Neowinian Senior

  • Tech Issues Solved: 10
  • Joined: 07-January 05
  • Location: London, UK

Posted 30 August 2014 - 00:06

Look into using transactions. Some things are just not possible using one SQL query.

 

http://dev.mysql.com...ansactions.html