PHP function problem

[Utherr12]

Hi there, i've just started learning php some days ago so i'm new at this. I'm having trouble with a simple form which i want it to send data to a sql database. The actual sending of the data works but i made a function to check if the $username && $mail are different from what i have in DB.The form from my main page looks like this:

<form method="post" action="test.php" ><input id="username" name="Username" class="reg" type="text" value="username" onchange="RequiredPHP()" /><input id="mail1" class="reg" name="Email" type="text" value="Email" onchange="CheckMailPHP()" onblur="RequiredPHP()" /><input id="Submit" name="Submit1" type="submit" value="Submit to DB" disabled="disabled" />

The php code on test.php page looks like this:

<?php$phpDB = mysql_connect("ip:port","xxxxxx","xxxx");mysql_select_db("php",$phpDB);function checkCred($username, $mail){	$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);	$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);		$username_db = mysql_fetch_array($username_array,MYSQL_ASSOC);	$mail_db = mysql_fetch_array($mail_array,MYSQL_ASSOC);	if (($username!=$username_db['name'])&&($mail!=$mail_db['mail']))		{return 1;}			//data can be inserted, entry in db does not exist		else {return 0;}	//data cannot be inserted, entry in db exists}$username = strtolower($_POST["Username"]);$mail = strtolower($_POST["Email"]);/* USED FOR DEBUGGING$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);$username_db = mysql_fetch_array($username_array,MYSQL_ASSOC);$mail_db = mysql_fetch_array($mail_array,MYSQL_ASSOC);echo checkCred($username, $mail)."<br />";echo $username_db['name']."<br />";echo $mail_db['mail']."<br />";echo "This is $username: ".$username."<br />";echo "This is $mail: ".$mail."<br />";*/if ($_POST["Username"]=="username")	//ignore this if, the next else if is important one which works	{ echo "You didn't actually do anything! Submit a different username.<br />";}	else if (checkCred($username, $mail)==1)				{ $insert_db = mysql_query("INSERT INTO `test_table` (`name`,`mail`) VALUES ('$username','$mail');",$phpDB);				echo $_POST["Username"]." accepted.<br />"; }					else	{ echo "<p style=\"font-family:Tahoma;font-size:x-large\">Account name \"".$_POST["Username"]."\" or e-mail \"".$_POST["Email"]."\" already exists in database!!!</p>";}mysql_close($phpDB);?>

If i use the debug method i did in the comment and i type the same $username and $mail the function returns 1, $username_db['name'] (the sql query SELECT) returns the matching $username, same with mail. I also included to echo the two variables so i'd know if they are actually the same or not, and yes they are, it outputs exactly the same string as the sql query.IF i type a different username and mail the function STILL returns 1 but the sql query returns "" meaning no matching results was found. This lead me to the fact that my function is not working properly, the rest of the code is fine, i also switched the return values in the if else statement, then the function returned 0, so no matter what i type the if statement is always true.Any suggestion how to fix this? Or how to improve it?

[MrFish]

You're doing pretty well for being only a few days into php. Your problems is you are using the wrong function to convert the results to an array, or using the wrong method for printing the array

mysql_fetch_array($query)

will convert the mysql resource into an array where the index is numerical.$username_db[0]

mysql_fetch_assoc($query)

will convert the mysql resource into an array where the index is a string.$username_db['name']generally if the array length is going to be 1 value I use array. Otherwise the php will have to check each array item if it's index string matches the index string you gave it. Which helps programmer readability but computers can compare numbers much quicker. You save a fraction of a second, it's up to you.Also, I'm not sure how your databases are setup. But if each user has 1 email per username, wouldn't a users table hold that information as well? That's the way I do it. Again, up to you

[Utherr12]

Ok, so i modified the if statement into:

if (($username!=$username_db[0])&&($mail!=$mail_db[0]))

Should be ok, right? Btw i used PHP & SQL for dummies book and to be honest i don't really understand what does the second parameter in the function mysql_fetch_array() does... i used MYSQL_ASSOC because i felt it was the right one :-s... could some1 care to explain through examples please?LE: Did the above stated changes and still doesn't work, but now the array that stores query has nothing in it ... $username_db[0] shows nothing some with mail. (i'm mentioning that the data i've entered already exists in db).the sql table is simple i made it this way for educational purposes (learning php with databases), i already know the sql language before php and javascript.

CREATE TABLE `test_table` (	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,	`name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'latin1_general_ci',	`mail` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'latin1_general_ci',	PRIMARY KEY (`id`))COLLATE='latin1_general_ci'ENGINE=MyISAMROW_FORMAT=DEFAULTAUTO_INCREMENT=29

And as a "side-note" whats the function that opens a new window & URL or to replace the current one. And another question: can php and javascript be linked somehow ? lets say i want to include in php a value that's returned by a js function.

[MrAdam]

mysql_fetch_array() returns both a numerical and associative array of the results. That means every field will be returned twice and can be accessed through either $result[0] or $result['field_name']. The optional second parameter for this function allows you to specify the result type (MYSQL_ASSOC(iative), MYSQL_NUM(erical), or the default MYSQL_BOTH).mysql_fetch_assoc() returns just the associative array ($result['field_name']), and is basically a quicker way to write mysql_fetch_array($query, MYSQL_ASSOC).mysql_fetch_row() returns just the numerical array ($result[0]), and is the short form way to use mysql_fetch_array($query, MYSQL_NUM).mysql_result() is better to use if you're only returning 1 field:

$username_db = mysql_result($username_array, 0);

$username_db would be a string by the way, you wouldn't access it through an index like $username_db[..], you use it directly in the comparison:

if ($username != $username_db && $mail != $mail_db)

Hope this clears things up

[Utherr12]

Thanks a million now everything works like a charm . I used mysql_result() because i only need to check if at least one exists. Another quick question: does mysql_result onkly works in conjuncture with SELECT operation in sql ?

[MrAdam]

Almost. mysql_result() requires a 'result resource' for it's first parameter. The only statements that return results are select, describe, explain and show. Update, insert, delete, etc. only return true/false.What is it you're trying to do?

[Utherr12]

I'm not trying to do anything in particular, just wanted to know some more about mysql functions in php so if i want to do something else in the future i'd know in advance what function and sql should i use. Since you answered my question dead on, i don't have any further questions .LE: Ok, in fact i have a question does mysql_result on a INSERT DELETE return false if the inseration failed? when does it return false and when true?

[MrAdam]

mysql_result() isn't used for executing a query, only for returning data from a result.mysql_query() you need to use to actually execute the query; including insert, update, delete, (...) statements. However the the return values may not be what you expect. If the query is executed successfully the function will return true, but that's still the case even if the query affects zero rows.A very rough example:

$query = mysql_query("delete from table where field=1");if (!$query){	echo 'record could not be deleted';}

This is wrong! $query will still be true even if no records were deleted.To validate if the query did as you expected you need to use mysql_affected_rows():

$query = mysql_query("delete from table where field=1");if (mysql_affected_rows($query) == 0){	echo 'record could not be deleted';}

Really the only time mysql_query() will return false is if there's a MySQL error.

[Utherr12]

Really the only time mysql_query() will return false is if there's a MySQL error.

Thanks this should help me, that's what i wanted to know .Btw, i still have problems with my page, it worked for a while. I think it "stopped working" after i manually deleted all entries in my DB using HeidiSQL except two and reset the auto-increment to 2. But I honestly don't think this has anything to do with my code.Now the problem is reversed, no matter what I type my function returns 0 (false).

function checkCred($username, $mail){	$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);	$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);		$username_db = mysql_result($username_array,0);	$mail_db = mysql_result($mail_array,0);	if (($username!=$username_db)&&($mail!=$mail_db))		{return true;}			//data can be inserted, entry in db does not exist		else {return false;}	//data cannot be inserted, entry in db exists}if ($_POST["Username"]=="username")	//ignore this if, the next else if is important one which works	{ echo "You didn't actually do anything! Submit a different username.<br />";}	else if (checkCred($username, $mail)==true)				{ $insert_db = mysql_query("INSERT INTO `test_table` (`name`,`mail`) VALUES ('$username','$mail');",$phpDB);				echo $_POST["Username"]." accepted.<br />"; }					else	{ echo "<p style=\"font-family:Tahoma;font-size:x-large\">Account name \"".$_POST["Username"]."\" or e-mail \"".$_POST["Email"]."\" already exists in database!!!</p>";}mysql_close($phpDB);

I changed 1 to true and 0 to false. I only modified the function, the rest of the code remains the same (except the ==true in the IF).LE: Ok here's with what I came up with:1. If this part of the code is like this:

/*$username = strtolower($_POST["Username"]);$mail = strtolower($_POST["Email"]);$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);$username_db = mysql_result($username_array,0);$mail_db = mysql_result($mail_array,0);echo checkCred($username, $mail)."<br />";echo $username_db."<br />";echo $mail_db."<br />";echo "This is #username: ".$username."<br />";echo "This is #mail: ".$mail."<br />";*/

the function always returns false.2. If this part of the code is like this:

$username = strtolower($_POST["Username"]);$mail = strtolower($_POST["Email"]);/*$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);$username_db = mysql_result($username_array,0);$mail_db = mysql_result($mail_array,0);echo checkCred($username, $mail)."<br />";echo $username_db."<br />";echo $mail_db."<br />";echo "This is #username: ".$username."<br />";echo "This is #mail: ".$mail."<br />";*/

]then the function always returns true.Here's the result of my debugger:1 // function valueusername // first row of the resulted SQL query on column usernameemail@yahoo.com // first row of the resulted SQL query on column mailThis is #username: username //$username = _$POST["Username"] value (the one entered in the form)This is #mail: email@yahoo.com //$mail = _$POST["Mail"] value from the formusername accepted.So as you can see those two strings ARE IDENTICAL ! Yet my function returns true.----------------------------------------------------------------------Later LATER edit: nevermind I removed the function and put everything directly like this

$username = strtolower($_POST["Username"]);$mail = strtolower($_POST["Email"]);$username_array = mysql_query("SELECT `name` FROM `test_table` WHERE `name` = '$username';",$phpDB);$mail_array = mysql_query("SELECT `mail` FROM `test_table` WHERE `mail` = '$mail';",$phpDB);	$username_db = mysql_result($username_array,0);$mail_db = mysql_result($mail_array,0);if (($username!=$username_db)&&($mail!=$mail_db))	{$insert_db = mysql_query("INSERT INTO `test_table` (`name`,`mail`) VALUES ('$username','$mail');",$phpDB);echo $_POST["Username"]." accepted.<br />";}			//data can be inserted		else {echo "<p style=\"font-family:Tahoma;font-size:x-large\">Account name \"".$_POST["Username"]."\" or e-mail \"".$_POST["Email"]."\" already exists in database!!!</p>";}	//data cannot be inserted, entry in db exists