PHP MySQL Select Query errors

Couldn't execute select QUERY

[jlt99]

I'm working wiht MySql Server version: 4.1.20-max-log
and PHP.

/* check whether user name already exists */
$sql = "SELECT loginName FROM Member
WHERE loginName = '$loginName'"; #177
$result = mysql_query($cxn,$sql)
or die("Couldn't execute select QUERY 4.");

I just can't seem to solve this problem. Everything seems ok, I've tested that I can connect to the database, which I can and it still shows and error.

I think I could have a syntax error but not 100% sure.

Any help please.

Thanks!

J

[eeek]

Have you considered printing out the error message to see what it says? Sure would be guessing.

[brotherhood of LAN]

yes, mysql_error() can be helpful.

$result = mysql_query($cxn,$sql)

It looks like you have your variables the wrong way round there.

[jlt99]

This is What I have for the Vairable build and an include file with the correct info in it.

$cxn = mysql_connect($host, $user, $passwd, $dbname)
or die ("Couldn't connect to SERVER 1."); #15

$sql = "SELECT loginName FROM Member
WHERE loginName='$_POST[fusername]'"; #18
$result = mysql_query($cxn,$sql)
or die("Couldn't execute QUERY 2."); #20

Still not got it yet.

J

[brotherhood of LAN]

$result = mysql_query($cxn,$sql)

should read

$result = mysql_query($sql,$cxn)

[jlt99]

After running an error check I get 0: 0:

[jlt99]

Here is the whole code for the section.

I have changed the $cxn,$sql around but still no joy....

/* check whether user name already exists */
$cxn = mysql_connect($host, $user, $passwd, $dbname). "\n";
echo mysql_errno($cxn) . ": " . mysql_error($cxn);

$sql = "SELECT loginName FROM Member
WHERE loginName = '$loginName'"; #177

$result = mysql_query($sql,$cxn). "\n";
echo mysql_errno($result) . ": " . mysql_error($result);

$num = mysql_num_rows($result); #180
if ($num > 0) #181
{
$message_new = "$loginName already used.
Select another User Name.";
include("login_form.inc");
exit();
}
/* Add new member to database */
else #190
{
$today = date("Y-m-d");
$fields_str = implode(",",$fields);
$values_str = implode('","',$values);
$fields_str .=",createDate";
$values_str .='"'.",".'"'.$today;
$fields_str .=",password";
$values_str .= '"'.","."md5"."('".$password."')";
$sql = "INSERT INTO Member ";
$sql .= "(".$fields_str.")";
$sql .= " VALUES ";
$sql .= "(".'"'.$values_str.")";
$result = mysql_query($sql,$cxn). "\n";
echo mysql_errno($result) . ": " . mysql_error($result);

$_SESSION['auth']="yes"; #205
$_SESSION['logname'] = $loginName; #206

/* send email to new member */ #208
$emess = "You are now registered... ";

$emess.= " email webmaster@#*$!#*$!.com";
$ehead="From: member-desk@#*$!#*$!.com\r\n"; #216
$subj = "Thank you, you are now registered...;
$mailsnd=mail("$email","$subj","$emess","$ehead");
header("Location: New_member.php"); #219
}
break; #221

default: #223
include("login_form.inc");
}

[jlt99]

Could this be an indexing problem? Like having the name Primary instead of the Index name?

[sonjay]

Help us out here.... do you get an error message? What does it say? Does the first query work but the second one doesn't? Or do neither of them work?

Have you echoed out the actual query to see if it's querying what you think it's querying?

Have you tried running the query in phpMyAdmin or on the command line?

[jlt99]

ok,

Well I am still getting familiar with php and mysql, as its been a long time away from programming.

At this stage the only errors I get are once the script is comeplete, the page goes blank and I get this :::

I don't get any sql error numbers the way things are right now, so maybe I am missing a basic some basinc syntax and application of the functions here maybe.

[Demaestro]

Do you know where the error_log lives in your site?

It should be in a folder named log or logs and that should be one folder up from your public html folder.

try the method, then load error_log into a text editor and see what is at the end of the file. An sql error, a php error, no error. Everything should be time stamped so note the time you try the method then check the log.

Without an error message it is really hard to tell what the problem is.

[edited by: Demaestro at 9:57 pm (utc) on April 6, 2009]

[eeek]

Could this be an indexing problem?

No. So why don't you tell us what the error message says?

[jlt99]

No access to the logs, as the ISP has them locked away. So no pessages except the one si displayed in my earlier message.

0: 0: and :::

That's it.

[eeek]

No access to the logs,

Do change the display level and read it on the web page. Better yet, get a provider that doesn't hide the error logs from its users.

[rocknbil]

Try this. Find this section of your code.

$cxn = mysql_connect($host, $user, $passwd, $dbname). "\n"; 
echo mysql_errno($cxn) . ": " . mysql_error($cxn); 
$sql = "SELECT loginName FROM Member 
WHERE loginName = '$loginName'"; #177 
$result = mysql_query($sql,$cxn). "\n"; 
echo mysql_errno($result) . ": " . mysql_error($result);  

First, you have already opened the DB resource, so you don't need to pass it again via mysql_query, especially if you're only using one per program. Second, remove the newlines on those commands/queries, I have no idea what effect they are having but they are not needed.

Third, the way you have it coded it is always going to try and print an error. Note in my revision, it only prints an error if there IS one. As you saw, it prints a blank string when there is no error, and this is probably just confusing you.

Don't worry about error_no, the text description should be sufficient to debug.

mysql_error doc [us3.php.net]

Return Values
Returns the error text from the last MySQL function, or '' (empty string) if no error occurred.

Last, note the lines for header, echo, and exit - you can remove/move them after you've determined the problem. Move them on down to your next select and debug it in the same way.

$cxn = mysql_connect($host, $user, $passwd, $dbname); 
$sql = "SELECT loginName FROM Member 
WHERE loginName = '$loginName'"; #177 
header("content-type:text/html\n\n"); # remove/move after debug 
$result = @mysql_query($sql); 
if (!$result) { 
 echo "error: " . mysql_error() . "<br>\n";  
} 
$row=mysql_fetch_array($result); 
echo "result found " . $row[0] . " with select $sql <br>\n"; # remove after debug 
mysql_free_result($result); 
exit; #remove after debug 

mysql_query docs [us3.php.net]

When you print things to the browser, associate it with a piece of text so you know what's supposed to be there. See my debugging echo line for an example.

Last bit of debugging advice, if you have any kind of place to enter your selects directly into mysql, either via command line or a control panel interface, do so to make sure the select actually works, as mentioned.

[jlt99]

Thanks for your reply, you are the first person to really look and gave a considered opinion. I was really hoping someone would give me feedback on syntaxt with a little help.

I stayed up all night trying everything and what I discovered was that; I was not getting errors becasue there were no erros. Everything is working fine but, the data is not getting passed from the rest of the form attached. I suspect because I added a feild that has now hosed the rest of the php.

Guess php is very sensitive to change?

BTW - I did try everything you suggested and the others. Thanks!

[rocknbil]

People don't always have time to fully examine every nuance of the code, I often overlook the obvious myself when scanning a thread.

the data is not getting passed from the rest of the form attached. I suspect because I added a feild that has now hosed the rest of the php.

Not likely. If the script runs, and doesn't error, the only thing I can guess at is the script does not have code in place to manage the new field. You have to add it.

<textrea name="comments"></textarea>

$comm = $_POST['comments'];

....

$fields_str .=",comments";
$values_str .=",'".$comments."'";