[SOLVED] PHP - SQL Query Help

[tsilenzio]

Ok I know how to do php with no problem (mostly) or atleast so far. However if i have a table in a database set up called: users and the columns are: UserID [Auto Increased], Email, Username, TimeStamp, and Status.

 

How would I write a query to first make sure that the user who is in the process of registering isnt entering an email hasn't been used yet and also check to make sure that the loginname isnt already used?

 

And then later when they check their email they click the activation link (already coded so im not worried about that) but how would I change the Status field from 0 to 1? - [THIS BEING A COMPLETLY DIFF QUERY THEN THE OTHER]

 

Sorry this whole query thing is confusing but if i can see how its written a few times with the examples id need then it would help me out a great deal!

 

Thanks ALOT!

[per1os]

SELECT userid FROM users WHERE (email = 'email@addy.com' OR username = 'username')

UPDATE users SET status = 1 WHERE username = 'username'

[tsilenzio]

wow that was easier the nexpected xD lol ok but um for the first one how do i tell if there is or isnt one after it exectes? like will the results be a bool or and int if u assigned it to a variable?

[per1os]

$result = mysql_query("SELECT userid FROM users WHERE (email = 'email@addy.com' OR username = 'username')");

if (mysql_num_rows($result) > 0) {
    echo 'The username/email is already in use.';
}

 

If you want to be able to display a helpful error message you need to branch that off into 2 queries instead of just one so the user knows that it was the username  that was invalid etc.

[tsilenzio]

yea i got it form here thanx so much man!!

[tsilenzio]

ok so does the result thingy a boolean or does it return the number of affected thigns so i knwo for the future (books dont really tell me this =/)

[tsilenzio]

like $result = 1 if it went sucessful or $result = 0 if unsecessful?  :-\

[Barand]

$result will return false if the query fails due to an error.

 

Finding zero rows is not an error, so it will not return false just because there is no matching data.

[tsilenzio]

ok and lastly when i use the select statement how does it retun it with the fetch_array()?

 

And are the elements number 0 thru (columns - 1) or the elements are the name of the columns? or fields or what ever they are called?

 

 

[per1os]

like $result = 1 if it went sucessful or $result = 0 if unsecessful?  :-\

 

$result is neither. It is a mysql resource. Even if the query returns 0 rows, it is still a valid query. That is why you have to use mysql_num_rows to check.

 

www.php.net/mysql_query

 

Returns a MySQL Resource ID. Which is used by mysql_fetch_array etc to retrieve the data.

[Barand]

With mysql_fetch_array() the answer is BOTH

 

$row = mysql_fetch_row()

  use $row[0], ..., $row[n]

 

$row = $mysql_fetch_assoc()

  use $row['colname1'], ... , $row['colnameN']

 

$row = $mysql_fetch_array()

  use either form