Checking and Updating DB

[jimmyp3016]

Hey Guys,

Im having a problem with one of my scripts. Whenever I submit to this form it always errors out at error2.html even though the username is not in the database. What could be wrong?

[code]
<?php
include "../config-site.php";

// Open database...
$db = mysql_connect("$localhost", "$databaseuser", "$databasepasswd");
mysql_select_db("$databasename",$db);


// Check and Update affiliate data...
$sql="SELECT userName FROM user WHERE userName='$user'";
$result = mysql_query("$sql",$db);
if (mysql_num_rows($result) != 0) {
  header("Location:error2.html");
  exit;
}

$sql = "SELECT * FROM user WHERE id='$userID'";
$sql = "UPDATE user SET firstName = '$firstname', lastName = '$lastname', phone = '$phone', company = '$company', website = '$website', aboutme = '$aboutme' WHERE id='$userID'";
$result = mysql_query($sql,$db) || die("Can't query DB: ". mysql_error());


// Close database...

mysql_close($db);
?>[/code]

Any help would be great thanks!

[trq]

Where do you define $user?

[jimmyp3016]

Hey Thorpe,

Thanks for responding so fast.

I define user up at the top. (sorry i didnt put it in)  $user =  $_GET['userName'];

I verified that it is actually grabbing it to. Do you know what could be the problem?

It gets hung up on the check and errors out to error2.html everytime.

Ive been troubleshooting for about an hour.

Any help would be great thanks.

[Cagecrawler]

Would this make any difference?
Change:
[code]if (mysql_num_rows($result) != 0) {[/code]
to
[code]if (mysql_num_rows($result) !== 0) {[/code]

I've always used == in if statements, I don't know if it's neccessary though.

[jimmyp3016]

Cage,

That did not work. It still errors to error2.html

[Cagecrawler]

Try removing the "" from around $sql, $localhost, $databaseuser, $databasepasswd and $databasename.

[jimmyp3016]

Still didnt work!

Ahhhh

Im going out of my mind haha!

[Cagecrawler]

Use "or die(mysql_error())" to debug your connection and query - see if anything comes up.

[jimmyp3016]

No error.

It just goes to a blank page when i have it go to [code]die(mysql_error());[/code]

[jimmyp3016]

Let me make sure im not going out of my mind and ill break down the code

SELECT userName FROM user WHERE userName='$user'

userName is the field in the database

user is the table name

$user is defined at the top of the page

Thats correct right? I dont know why its not working.

[trq]

Try...

[code=php:0]
$sql = "SELECT userName FROM user WHERE userName='$user'";
if ($result = mysql_query($sql,$db)) {
  if (mysql_num_rows($result) > 0) {
    header("Location: error2.html");
    exit;
  }
}
[/code]

[Jessica]

What do you get when you echo mysql_num_rows($result)

[jimmyp3016]

Here is my whole script so we can get to the bottom of this.

[code]<?php

$userID = $_GET['id'];
$user = $_GET['userName'];
$firstname = $_GET['firstName'];
$lastname = $_GET['lastName'];
$address             = $_GET['address'];
$city = $_GET['city'];
$state = $_GET['state'];
$zip = $_GET['zip'];
$phone = $_GET['phone'];
$company = $_GET['company'];
$website             = $_GET['website'];
$aboutme = $_GET['aboutme'];


if ( ($user=="") || ($firstname=="") || ($lastname=="") || ($phone=="") || ($company=="") || ($website=="") )
{
    header("Location:error1.html");
    exit;
}


include "../config-site.php";
include 'corpmessage.php';

// Open database...
$db = mysql_connect("$localhost", "$databaseuser", "$databasepasswd");
mysql_select_db("$databasename",$db);


// Check and store affiliate data...
$sql="SELECT userName FROM user WHERE userName='$user'";
$result = mysql_query("$sql",$db);
if (mysql_num_rows($result) != 0) {
    header("Location:error2.html");
  exit;
}

$sql = "SELECT * FROM user WHERE id='$userID'";
$sql = "UPDATE user SET firstName = '$firstname', lastName = '$lastname', phone = '$phone', company = '$company', website = '$website', aboutme = '$aboutme' WHERE id='$userID'";
$result = mysql_query($sql,$db) || die("Can't query DB: ". mysql_error());


// Close database...

mysql_close($db);


// Send message to inform webmaster about the new affiliate...
$subject="$firstname $lastname has signed up with $sitename";
mail("$affiliaterecipient","$subject","$mailbody","From: $siteemail \nContent-Type: text/plain; charset=iso-8859-1\nContent-Transfer-Encoding: 8bit");


// Show thank you message...

header("Location:invite.php?id=$userID");
?>[/code]

[Cagecrawler]

Thorpe, surely you need to define $result to use it in an if statement?

Jimmy, what exactly did you do to get this:
"It just goes to a blank page when i have it go to
Code:
die(mysql_error());"


Try this:
[code]// Open database...
$db = mysql_connect("$localhost", "$databaseuser", "$databasepasswd") or die(mysql_error());
mysql_select_db("$databasename",$db) or die(mysql_error());


// Check and Update affiliate data...
$sql="SELECT userName FROM user WHERE userName='$user'";
$result = mysql_query("$sql",$db) or die(mysql_error());
$numrows=mysql_num_rows($result) or die(mysql_error());
if ($numrows != 0) {
  header("Location:error2.html");
  exit;
}[/code]

[trq]

[quote]Thorpe, surely you need to define $result to use it in an if statement?[/quote]

Why would you? $result = mysql_query($sql,$db) is an expression, expressions return true or false.

[jimmyp3016]

[quote]
Jimmy, what exactly did you do to get this:
"It just goes to a blank page when i have it go to
Code:
die(mysql_error());"[/quote]


I commented out the header to error2.html and added die(mysql_error());

[Cagecrawler]

ahh... or die(mysql_error()) should be posted after mysql fuctions.

Use the code I posted a couple of posts ago.

EDIT:
Here it is:
[code]// Open database...
$db = mysql_connect("$localhost", "$databaseuser", "$databasepasswd") or die(mysql_error());
mysql_select_db("$databasename",$db) or die(mysql_error());


// Check and Update affiliate data...
$sql="SELECT userName FROM user WHERE userName='$user'";
$result = mysql_query("$sql",$db) or die(mysql_error());
$numrows=mysql_num_rows($result) or die(mysql_error());
if ($numrows != 0) {
  header("Location:error2.html");
  exit;
}[/code]

[quote]
[quote]Thorpe, surely you need to define $result to use it in an if statement?[/quote]
Why would you? $result = mysql_query($sql,$db) is an expression, expressions return true or false.[/quote]

Isn't = not a comparison expression.  If you want it to return TRUE or FALSE, then wouldn't you have to define $result and use $result == mysql_query($sql,$db)?  $result = mysql_query($sql,$db) makes $result equal to the query, so it will always equal.

[Jessica]

psst.
[quote author=jesirose link=topic=121872.msg501868#msg501868 date=1168481881]
What do you get when you echo mysql_num_rows($result)
[/quote]

[trq]

[quote]$result = mysql_query($sql,$db) makes $result equal to the query, so it will always equal[/quote]

Yes, and the query will return true or false.

You must be getting a row returned, have you tried this yet?

[code=php:0]
$sql = "SELECT userName FROM user WHERE userName='$user'";
if ($result = mysql_query($sql,$db)) {
  if (mysql_num_rows($result) > 0) {
    header("Location: error2.html");
    exit;
  }
}
[/code]

[jimmyp3016]

How do I echo mysqlnumrows correcty?

It does connect to the db because if i comment out the whole check part like :
[code]
// Check and Update affiliate data...
//$sql="SELECT userName FROM user WHERE userName='$user'";
//$result = mysql_query("$sql",$db) or die(mysql_error());
//$numrows=mysql_num_rows($result) or die(mysql_error());
//if ($numrows != 0) {
// header("Location:error2.html");
// exit;
}[/code]

Then it updates to the database correctly.

But I need the checking to work because I cannot have duplicate usernames.

[Jessica]

before if($numrows) add echo $numrows;

[jimmyp3016]

This is what I get when I echo that.

2
Warning: Cannot modify header information - headers already sent by (output started at /home/content/p/o/w/powerpage/html/mem/signup.php:35) in /home/content/p/o/w/powerpage/html/mem/signup.php on line 37

[trq]

See that 2? That meens you have 2 users allready with said username. The header error will go away when you remove the debugging echo $numrows.

[jimmyp3016]

Its weird because i have 3 people on that table and 1 has a username. The others do not.

If there are blank usernames do you think this is why its causeing the error of 2?

Because 2 out of the 3 people in the table have blank usernames?

If so ill have to set a default name so when people fill in the rest of there data (real username) they wont have a problem.

Does this sound good?

[trq]

[quote]Does this sound good?[/quote]

No. Are you positive $user is being set? Put this at the very top of your script and post its output here.

[code]
<?php print_r($_GET); ?>
[/code]