how do i check existence of field in table then insert or update?

rupertbj

Please see the following code....

<?php
include("admin.inc");
if ((!ereg(".+\@.+\..+", $user)) || (!ereg("^[a-zA-Z0-9_@.-]+$", $user)))
	{
    print "
	<body bgcolor=\"#8080FF\">
	<b>Invalid Email address! Please <a href=/index.html>register</a> properly!!!</b>
	";
	exit;
	}
else
	{
	setcookie( "user", $user, "0" );
	$db = "register";
	$link = mysql_connect( "localhost:/users/rupertbj/domains/rupstar.co.uk/sql/mysql.sock", "root" );
	if ( !$link ) die( "Couldn't connect to MySQL" );
	mysql_select_db( $db, $link ) or die( "Couldn't open $db: ".mysql_error() );
		if ( $sql = "SELECT user FROM userTable WHERE user = '$user'";
		mysql_query( $sql, $link );
		mysql_close( $link ); )
		{
		$query = "UPDATE userTable SET date_time = now() WHERE user = $user";
		mysql_query( $query, $link );
		mysql_close( $link );
		}
		else
		{
		$sql = "INSERT INTO userTable VALUES ( '$user', now() )";
		mysql_query( $sql, $link );
		mysql_close( $link );
		}
	}
?>

This brings up an error message:
Parse error: parse error in /users/rupertbj/domains/rupstar.co.uk/html/mysqllogin.php on line 18

line 18 reads:

if ( $sql = "SELECT user FROM userTable WHERE user = '$user'";

Please can someone help me out?

Thanks in advance for your help!

notionlogic

Maybe it's just me but I've never seen an IF statement presented like that...

Also, you're working on the assumption that the query will FAIL if the value of $user is not found which it won't - it will just return no rows !

rupertbj

So, what should I be doing? - I'm afraid I'm still learning and I've given it my best shot, but need more input!

Steffel

its just like notionlogic said:

so u could use the numer of resultrows for your if statement

$sql = "SELECT user FROM userTable WHERE user = '$user'";
$result = mysql_query($sql, $link);

if (mysql_num_rows($result) > 0)
--> update
else
--> insert

notionlogic

Something like this would do it...

$result = mysql_query("SELECT user FROM userTable WHERE user = '$user'", $link);
$num_rows = mysql_num_rows($result);

if ($num_rows > 0)
{
$query = "UPDATE userTable SET date_time = now() WHERE user = $user";
mysql_query( $query, $link );
}
else
{
$sql = "INSERT INTO userTable VALUES ( '$user', now() )";
mysql_query( $sql, $link );
}
mysql_close( $link );

Does that help?

Of course the better way would be to use the REPLACE syntax that MySQL offers 😃

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted.

rupertbj

OK,

nearly there - this is the complete code i have now, but a parse error occurs on line 74 which reads " <HTML> "

<?php
include("admin.inc");
if ((!ereg(".+\@.+\..+", $user)) || (!ereg("^[a-zA-Z0-9_@.-]+$", $user)))
	{
    print "
	<body bgcolor=\"#8080FF\">
	<b>Invalid Email address! Please <a href=/index.html>register</a> properly!!!</b>
	";
	exit;
	}
else
	{
	setcookie( "user", $user, "0" );
	$db = "register";
	$link = mysql_connect( "localhost:/users/rupertbj/domains/rupstar.co.uk/sql/mysql.sock", "root" );
	if ( !$link ) die( "Couldn't connect to MySQL" );
	mysql_select_db( $db, $link ) or die( "Couldn't open $db: ".mysql_error() );
	$result = mysql_query("SELECT user FROM userTable WHERE user = '$user'", $link); 
	$num_rows = mysql_num_rows($result); 
	if ($num_rows > 0) 
	{ 
	$query = "UPDATE userTable SET date_time = now() WHERE user = '$user'"; 
	mysql_query( $query, $link ); 
	} 
	else 
	{ 
	$sql = "INSERT INTO userTable VALUES ( '$user', now() )"; 
	mysql_query( $sql, $link ); 
	} 
	mysql_close( $link ); 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN">
<html>
<head>
<meta http-equiv="Page-Enter" CONTENT="RevealTrans(Duration=2,Transition=2)">
<meta http-equiv="Page-Exit" CONTENT="RevealTrans(Duration=2,Transition=2)">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="format.css" type="text/css">
<title>This is rupstar.co.uk...</title>
</head>
<body bgcolor="#8080FF">
	<p align=center><img src="/images/rupstar.gif"></p>
	<table width=100% cellpadding=5 cellspacing=5 border=0>
	<tr>
	<td valign=top width=30% bgcolor=#88BB77 class=text>

  <table width=100% class=text>
	<tr> 
	  <td>Pick an option...</td>
	</tr>
	<tr> 
	  <td bgcolor=#666666 align=center><a href="dark.php">Photos</a></td>
	</tr>
	<tr> 
	  <td bgcolor=#CC3333 align=center><a href="gossip.php">Forum</a></td>
	</tr>
	<tr> 
	  <td bgcolor=#009900 align=center><a href="green.php">Events</a></td>
	</tr>
	<tr>
	  <td bgcolor=#FFFF33 align=center><a href="contact.php">Contact</a></td>
	</tr>
  </table>
</td>
<td valign=top width=70% bgcolor=#AA7799>
<span class=text>Welcome to the interior of the site!!!</span>
  <p><?php echo $interior ?></p>
</td>
</tr>
</table>
<p class=mini>Designed by<b> </b><a href="mailto:design@rupstar.co.uk"><b>rupstar</b></a><b> 
  &copy; 2002</b></p>
</body>
</html>

Parse error: parse error in /users/rupertbj/domains/rupstar.co.uk/html/mysqllogin.php on line 74

Steffel

how about closing your else brackets???

rupertbj

thanks!

I hate it when it's that simple!!!!

rupertbj

Warning: Supplied argument is not a valid MySQL result resource in /users/rupertbj/domains/rupstar.co.uk/html/mysqllogin.php on line 19

Now I really don't understand this error message? any ideas?