Select query problem

DGK101 · Dec 11, 2004

Hi there,

I have a problem in one of my scripts.

What I want to do is store names and email addresses into a mysql DB, but I have an if statement with a query that checks if the same email address is in the DB already, if it is not, the name and email address will be stored.

My problem is that the select statement does not execute properly,it sets my result variable as having a value in it,when it does not.

I have played around with the syntax near the variable value, thinking it was that, but to no avail.

My insert statement works perfectly...

Please help! I have sat for ever on this problem.

The code I have is as follows:

<?php
$name = array($_POST['nameone'],$_POST['nametwo'],$_POST['namethree'],$_POST['namefour'],$_POST['namefive'],$_POST['namesix'],$_POST['nameseven'],$_POST['nameeight'],$_POST['namenine'],$_POST['nameten']);
$email = array($_POST['emailone'],$_POST['emailtwo'],$_POST['emailthree'],$_POST['emailfour'],$_POST['emailfive'],$_POST['emailsix'],$_POST['emailseven'],$_POST['emaileight'],$_POST['emailnine'],$_POST['emailten']);
$cell = $_POST['cell'];
?>
<html>
<head>
<title>Emails Entered</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php

if (!$name[0] || !$email[0])
{
echo 'You have not entered any data in the fields, please go back and start at field one. Thank You.';
echo '<br><a href="emailenter.php">Back<a>';
exit;
}

$db = mysql_pconnect('localhost', 'root', '');

if (!$db)
{
echo 'Error connecting to the database';
exit;
}

$count = 0;

for ($i = 0; $i < 11 ; $i++)
{

if ($email[$i])
{

mysql_select_db('emaildb');
$query = "select * from emails where email like ".$email[$i]."";
$result = mysql_query($query);

if (!$result)
{
mysql_select_db('emaildb');
$query = "insert into emails values
(NULL,'".$cell."','".$name[$i]."', '".$email[$i]."')";
$result = mysql_query($query);
$count++;
}
}

if ($i == 10)
{
echo 'You have successfully entered '.$count.' email addresses, Thank You';
exit;
}
}

?>
</body>
</html>

Vragabond · Dec 11, 2004

Your problem lies here:

Code:

if (!$result)

You are checking if $result is false, but it is not, since $result is false if the query is incorrect. Your query is perfectly ok, it justs produces an empty recordset. That is why $result is always true and your script assumes email exists. Try checking for number of rows returned instead:

Code:

if (mysql_num_rows($result) == 0)

DGK101 · Dec 11, 2004

you are the king!! thanks.

DGK101 · Dec 12, 2004

Sorry, I only made the changes today. I was so sure that it would solve my problem, but unfortunately it did not...

Any other ideas/solutions?

herman75 · Dec 13, 2004

Try to echo your queries, to see what it really sends to the database.

You say you do:

$query = "select * from emails where email like ".$email[$i]."";

If I read this correctly this is what happens:

$email is set to test@example.com

the following query is then send to the database:

SELECT * FROM emails WHERE email LIKE test@example.com

This does not seem correct.

First of all why do you use LIKE? If you don't use wildcards like %, LIKE does exactly the same as =, but slower.

Second of all you seem to miss quotes around the $email.

Maybe this will solve it?

$query = "select * from emails where email = '".$email[$i]."'";

DRJ478 · Dec 13, 2004

I strongly recommend that you add error handling to your MySQL commands. For example:

Code:

mysql_select_db('emaildb') OR die("Cannot connect: ".mysql_error());
$query = "select * from emails where email like ".$email[$i]."";
$result = mysql_query($query) OR die("Query error: ".mysql_error());

General suggestions:
You have all your e-mail addresses in the $_POST array. You need not create another array to assmeble them.
I would use foreach($_POST[] as $key=>$value) to iterate the POST array. When the $key is 'name....' extract the number part which you then use to get the email.

DGK101 · Dec 13, 2004

Thanks to all who posted, It was very informative!!

My script is working perfectly now. All your input added to the solution.

to DRJ478: Thanks for your suggestion, now created less code in my script.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Select query problem

DGK101

Programmer

Vragabond

Programmer

DGK101

Programmer

DGK101

Programmer

herman75

Programmer

DRJ478

IS-IT--Management

DGK101

Programmer

Similar threads

Part and Inventory Search

Sponsor