[RESOLVED] error :mysql select query..

[chandan_tiwari]

hi,

I am new to php. I have problem with the following code :
this is not generating any error but not selecting the username.
It is not giving me error when the same username already exists in the database.
The php code :

<?php
include 'common.php';
include 'db.php';

if(!isset($_POST['register']))
{
 header("Location=register.php");
}
else
{
 dbconnect('rishaorg_members');
}

if($_POST['newid']=='' or $_POST['firstname']=='' or $_POST['lastname']=='' or $_POST['address']=='' or $_POST['city']=='' or $_POST['state']=='' or $_POST['country']=='' or $_POST['birthcity']=='' or $_POST['birthstate']=='')
{
 error('One more required fields were left blank. \\n Please fill them in and try again.');
}
if ($_POST['newemail']=='')
{
header("Location: email_error.html");
exit;
}
if (! ereg('[A-Za-z0-9_-]+\@[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+', $_POST['newemail']))
{
header("Location: email_error.html");
exit;
}
$sql = "SELECT COUNT (*) FROM user WHERE userid = '$_POST[newid]'";
$result = mysql_query($sql);
if(@mysql_result($result,0,0)>0)
{ error('A user alreday exists with your chosen Username. \\n Please try another Username');
}  

$newpass=substr(md5(time()),0,6);
$query = "INSERT INTO user (userid, password, firstname, lastname, gender, address, city, state, country, email, phone, birthdate, birthmonth, birthyear, hour, minute, seconds, birthplace, birthplace1, agee) VALUES ('$_POST[newid]', PASSWORD('$newpass'), '$_POST[firstname]', '$_POST[lastname]', '$_POST[gender]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[country]', '$_POST[newemail]', '$_POST[phone]', '$_POST[birthdate]', '$_POST[birthmonth]', '$_POST[birthyear]', '$_POST[birthhour]', '$_POST[birthminute]', '$_POST[birthsecond]', '$_POST[birthcity]', '$_POST[birthstate]', '$_POST[agree]')";                                                         

mysql_query($query);
$message = " Congratulations ! 
Your personal account for the Rishi Jyotish Sanstha has been ceated !
We value your association with us.
To log in , proceed to the following address :
http://www.rishijyotishsanstha.org/index.php

Your personal login ID and password as follows :

	userid : $_POST[newid]
      password : $newpass

You can change your password at any time after you have logged in.

If you have any probleems feel free to contact us at :
http://www.rishijyotishsanstha.org/contactus.html


- Member's Administrator
|| Rishi Jyotish Sanstha || ";

mail($_POST['newemail'],"Member's Password for Rishi Jyotish Sanstha",$message,"From:Member Admin <[e-mail]>");
header("Location:http://www.rishijyotishsanstha.org/successfullregistration.php")
?>

[bradgrafelman]

How would you know if it's generating an error or not? Try executing your queries in this manner:

$result = mysql_query($sql) or die('SQL error: ' . mysql_error() . '<hr>Query: ' . $sql);

Also, some other critiques of your code:

1. You should use [man]preg_match[/man] instead of [man]ereg[/man]. The [man]PCRE[/man] functions are much more useful and efficient than the POSIX [man]regex[/man] functions. A good page about validating e-mails (that uses PCRE regexp) can be found here or by searching the forums.

2. You have two different if() statements that deal with the e-mail address; since the second checks if the e-mail address matches the regexp pattern, there is no reason to have the first if() statement check if the e-mail is empty.

3. There's no need to execute two different SQL queries. First of all, userid should have a primary or unique key (probably unique since you most likely have an auto_increment id field set as primary key.. right?). That way, you simply need to execute the INSERT query. If there was no error, then display the congrats message. If [man]mysql_errno/man returns 1062, then MySQL has found a duplicate key and the INSERT failed.

4. By directly placing POST'ed data in your SQL query you're leaving yourself vulnerable to SQL injection attacks. Google or search the board for the term to find what it means, but what you need to do is run any and all user-supplied data through a function such as [man]mysql_real_escape_string/man to sanitize it.

5. Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].

6. Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:

   echo 'Your name is: ' . $row['name'] . '! Hello there.';

[chandan_tiwari]

thank you for giving me such useful tips.
I have id which is primary key and set to auto incerment.
will you please let me know how can i check and insert the form fields using ID number so that new username is being checked with the existing username.
Thank you !

[tomhath]

If you have a unique index column on the userid column you will get an error when you try to insert another row with the same userid.

Update your database with this to add the index:

CREATE UNIQUE INDEX userid_ix ON user (userid)

[Roger_Ramjet]

chandan_tiwari wrote:

thank you for giving me such useful tips.
I have id which is primary key and set to auto incerment.
will you please let me know how can i check and insert the form fields using ID number so that new username is being checked with the existing username.
Thank you !

If id is autoinc then you can not insert a value into it - the db generates the id itself.

If you want user names to be unique then create a unique index on that column.