How to check for existence of a row without using INSERT IGNORE?

[Jeffro]

I currently use this query to update listings in my database from my php app:

$query = "INSERT INTO listings (title, description) VALUES ('$title','$description')";

 

This listings table has a 'postid' column as it's primary key that auto increments. 

 

I don't want to do an INSERT IGNORE and have it check postid.  Instead, I'd like to keep the table structure the same and check to see if $title exists.. and not insert if it does. 

 

Will php/mysql allow me to somehow run a: 

If ($title does not exist) {
$query = "INSERT INTO listings (title, description) VALUES ('$title','$description')";
}  

 

If so, how would I write that? 

 

Thanks for any suggestions. 

[requinix]

Run a SELECT query...

[sunfighter]

This worked for me. Try it. I think it's your answer.

 

$query = "

INSERT INTO listings (title, description)

SELECT '$title','$description' FROM customers

WHERE NOT EXISTS (

SELECT * FROM listings

WHERE title = '$title')

LIMIT 1";

 

$results = mysql_query($query);

[Jeffro]

This worked for me. Try it. I think it's your answer.

 

$query = "

INSERT INTO listings (title, description)

SELECT '$title','$description' FROM customers

WHERE NOT EXISTS (

SELECT * FROM listings

WHERE title = '$title')

LIMIT 1";

 

$results = mysql_query($query);

 

Thank you so much!  That is exactly what I needed!  Much appreciated! 

[Jeffro]

I may have jumped the gun with my congrats.  It appears you can't combine a where with an insert clause?

[sunfighter]

Did you get an error?

 

As I said it work for me over and over again. The WHERE is in the SELECT s.

[Jeffro]

Did you get an error?

 

As I said it work for me over and over again. The WHERE is in the SELECT s.

 

I didn't try it as I couldn't figure out how to insert values with your example?  You were pulling $title and $description from 'customers', so the where statement is a bit different.  I need to do an insert into listings ('title', 'description') values ('$title','$description') ... and then do a WHERE NOT EXISTS (SELECT * FROM listings WHERE title = '$title')  ...so not sure how I could write that or if it would work that way?  My insert values aren't coming from a select statement.  They're coming from the php code on the page. 

[sunfighter]

OK. I didn't return it to ALL the corect settings. After all I practiced on my database. lol

 

Change FROM customers

to

FROM listings

 

After all were working with only one table.

 

 

[Jeffro]

Thanks for the help sunfighter. 

 

What i was saying though is that you are getting your values from a select statement.  I am inserting values from php variables (for half my clause).. so I don't think that would work. 

 

In any case.. I finally got it (someone on a different forum provided the solution):

 

$result = mysql_query("SELECT count(*) as total from listings where title='$title'");
$result = mysql_fetch_array($result); 
if($result['total'] == 0){

$query = "INSERT INTO listings (title, description) VALUES ('$title','$description')";
mysql_query($query) or die( "<br>Query string: $query<br>Caused error: " . mysql_error() );
}

 

Works great.  Thanks again for giving it a whirl.  Much appreciated!