visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Check if already exists(PHP/MYSQL)
By: Squibi U.S.A.  Date: 22/04/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
This should be a simple problem. All i'm trying to do is check if a certain title exists in a database field and if it does then increment the variable named views. If not, insert the title into the table under the field gamename and set views = 0. I just wrote this code and am new to PHP/MYSQL. Assume I'm already connected to the database. $title is the variable that is passed to the php page. Please help me modify this code to work. Thanks in advance.


mysql_select_db ("cheatc1_thedb");

$query = "SELECT gamename FROM pl_saturn WHERE gamename = $title";
$result = mysql_db_query("cheatc1_thedb", $query);


if(!$result){
$query = "INSERT INTO saturn (gamename, views) values ($title, 0)";
}
else
{
$query = "UPDATE saturn SET views = (views + 1) WHERE gamename = $title";
}
By: VGR Date: 22/04/2003 08:29:00 English  Type : Answer
not that bad already :D

common mistakes with strings and quotes, and a big misunderstanding below but it's almost all :
if(!$result){ // NO! This means the DB was unaccessible

mysql_select_db ("cheatc1_thedb");

$query = "SELECT gamename FROM pl_saturn WHERE gamename = '$title'";
$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());


if(mysql_numrows($result)==0){
$query = "INSERT INTO saturn (gamename, views) values ('$title', 0)";
$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());
}
else
{
$query = "UPDATE saturn SET views = (views + 1) WHERE gamename = '$title'"; // would be faster to retrieve the ID above in first query
$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());
} // if
By: Ice_S5 Date: 23/04/2003 19:34:00 English  Type : Comment
This would also work:

$query = "SELECT COUNT(gamename) AS howmany FROM pl_saturn WHERE gamename = '$title'";
$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());


$query = (mysql_result($result,0,"howmany") ? "INSERT INTO saturn (gamename, views) values ('$title', 0)" : $query = "UPDATE saturn SET views = (views + 1) WHERE gamename = '$title'");

$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());
By: Ice_S5 Date: 23/04/2003 19:36:00 English  Type : Comment
Sorry for obvious mistake. This one is correct:

$query = (mysql_result($result,0,"howmany") ? "UPDATE saturn SET views = (views + 1) WHERE gamename = '$title'" : "INSERT INTO saturn (gamename, views) values ('$title', 0)" );
By: VGR Date: 23/04/2003 22:52:00 English  Type : Comment
still trying to do it one-line ? :D
what for ? :D

I respected the Asker's code layout. That's the way I prefer. The blocks in the if are not needed and the $result = mysql_db_query line should be unique and after the test, but anyway.

It works, no ? And doesn't do more instructions than needed.
By: Squibi Date: 23/04/2003 23:49:00 English  Type : Comment
VGR,
Where you said it would be faster to retrieve the ID in the first query, what do you mean by that and how would I do that?
By: VGR Date: 24/04/2003 05:19:00 English  Type : Comment
ex. :

mysql_select_db ("Squibi_thedb");

$query = "SELECT id,gamename FROM pl_saturn WHERE gamename = '$title'";
$result = mysql_db_query("cheatc1_thedb", $query) or die("bad query '$query', error was : ".mysql_error());


if(mysql_numrows($result)==0){
$query = "INSERT INTO saturn (gamename, views) values ('$title', 0)";
$result = mysql_db_query("Squibi_thedb", $query) or die("bad query '$query', error was : ".mysql_error());
}
else
{
$res=mysql_fetch_array($result); // supposed to be unique. num_rows() could be tested against 1
$locID=$res['id'];
$query = "UPDATE saturn SET views = (views + 1) WHERE id=$locID";
$result = mysql_db_query("Squibi_thedb", $query) or die("bad query '$query', error was : ".mysql_error());
} // if


// that is, if your id is an "integer unique auto_increment" or a primary key, then it has an index by default. Column 'gazmename' doesn't, unless you setup one - and I suggest you add "USE INDEX(gamename)" in the SELECT query if ever you choose to add an index

regards
By: Squibi Date: 24/04/2003 06:34:00 English  Type : Comment
I actually did create a ID variable with the auto_increment int type. I just didn't see any need for it in the code I had. I'm confused however as to why that last bit of code you posted is better than the first bit.
By: VGR Date: 24/04/2003 08:51:00 English  Type : Comment
just a question of speed. You auto_inc field 'id' has an index attached to it... retrieving/accessing records on the 'id' is faster than searching again for a string value in an other column.

do an EXPLAIN on both queries if you want, but here's a similar example :

mysql> explain select * from image_tbl where image_id=2;
+-----------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+-------+---------------+---------+---------+-------+------+-------+
| image_tbl | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

1 row to scan to access the data

mysql> explain select * from image_tbl where caption='Two men Sumo wrestling';
+-----------+------+-------------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+------+-------------------+------+---------+------+------+------------+
| image_tbl | ALL | caption,caption_2 | NULL | NULL | NULL | 1986 | where used |
+-----------+------+-------------------+------+---------+------+------+------------+
1 row in set (0.00 sec)

1986 rows to (full) scan to get the same data :D

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 306.952000 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page