[SOLVED] conditions in a mysql query

[unknown1]

I have created a script for my listing site and this script does is asks user to add a line of code to his/her site to verify that they are the owner of the sites...

 

<?php
session_start(); 
include("config.php");

$sql_site = ("SELECT * FROM sit_details_tmp WHERE `email` = '$_SESSION[email]' ");
$site_con = mysql_query($sql_site, $conn) or die(mysql_error());
$numrows = mysql_num_rows($site_con);

if($numrows > 0){
while($row = mysql_fetch_array($site_con))
{

$partnersURL="$row[url]";
$htmlString=file_get_contents($partnersURL);
$var_code = "$row[ver_code]";

if (eregi($var_code, $htmlString)) {

$ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '".$_SESSION[email]."'") or die(mysql_error());
if (!mysql_query($ver_update,$conn))
  {
  die('Error: ' . mysql_error());
  }

echo '<script language=javascript>';
echo 'alert("Validation code was found! \n Your site has been successfully verified.");'; 
echo '</script>';
echo '<SCRIPT language="JavaScript">
<!--
window.location="my_account.php";
//-->
</SCRIPT>
';
}else{
echo"$var_code";
echo '<script language=javascript>';
echo 'alert("Validation code was not found! \n Please make sure you have entered the correct \n validation code and that its on the index page of your site.");';
echo '</script>';
echo '<SCRIPT language="JavaScript">
<!--
window.location="my_account.php";
//-->
</SCRIPT>
';
}
}
}else{
echo"User not logged.";
}
?>

 

The script is working fine with one big flaw.

Say a user goes to verify the code put on her/her sites they add the code to one site but not the other and hit verify...

the script will update all sites as verified and not just the site that actually has the code.

 

I assume the problem is in the line

 

$ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '".$_SESSION."'") or die(mysql_error());

 

but I can't think of another way to write it so that it only updates the site that has the verification code.

 

If anyone has an idea of how I could fix this it sure would be appreciated.

 

Thanks in advance!!

[btherl]

If you are allowing more than one site for one email address then you'll need to store the site in the database as well as the email.  Then when you update it, you check the site as well as the email.

[unknown1]

If you are allowing more than one site for one email address then you'll need to store the site in the database as well as the email.  Then when you update it, you check the site as well as the email.

 

I do have email and url in the same table... I'm not sure I understand what your saying.

or do you mean add

AND Url = $row[url];

or something like that??

 

Oh and the site and email is already in the table what the script is doing is checking if $row; aka site Url  has $row['ver_code']; and ver_code contains the code that they enter on the index page.... if the site has ver_code then updates table.

 

 

Or maybe I just don't understand what you mean

 

 

 

 

 

[btherl]

Yes, exactly like that.  Then it will update only for that email and that url, not for ALL urls.  The way to include arrays inside a string is like this:

 

$sql_site = ("SELECT * FROM sit_details_tmp WHERE `email` = '{$_SESSION['EMAIL']}' ");

 

The {} characters "protect" the array and make sure it will be interpreted properly.  If you just use $row for example, then it MIGHT work, but it's much safer to use the {} and also to put single quotes around the 'Url' bit inside the [].

 

Good: $row['Url']

Risky: $row

[unknown1]

Yes, exactly like that.  Then it will update only for that email and that url, not for ALL urls.  The way to include arrays inside a string is like this:

 

$sql_site = ("SELECT * FROM sit_details_tmp WHERE `email` = '{$_SESSION['EMAIL']}' ");

 

The {} characters "protect" the array and make sure it will be interpreted properly.  If you just use $row for example, then it MIGHT work, but it's much safer to use the {} and also to put single quotes around the 'Url' bit inside the [].

 

Good: $row['Url']

Risky: $row

 

In my script I already have $sql_site = ("SELECT * FROM sit_details_tmp WHERE `email` = '$_SESSION['EMAIL']' "); and it is cycling through the users Url ok... what I mean by updates all sites is all sites that belong to that user. So that part is ok... The problem is if the user has 3 sites

and has to verify all of them the script cycles through the $row and $row[ver_code] for that user... and if the user puts the code on site but not the other the query $ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '".$_SESSION."'") or die(mysql_error()); updates all sites that belong to that users email and not just the the one site that contains the verification code.

 

I tried something like

$ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '".$_SESSION[email]."' AND  eregi($var_code,$htmlString) ") or die(mysql_error());

 

but get an error

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

 

 

[unknown1]

bump

[btherl]

The next step is to echo out your sql query so you can see the syntax error.  That eregi looks suspicious - you need at least single quotes around any string unless it's a special word for postgres (like "SELECT" or "UPDATE").

 

For example:

 

$ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '".$_SESSION[email]."' AND  eregi($var_code,$htmlString) ");
$result = mysql_query($ver_update) or die("Error in $ver_update: " . mysql_error());

[unknown1]

I have now fixed the original issues but now have a new one.

 

The script is now working and doing what it should... but I need some advice on how to rewrite it so that it only redirects after both JavaScript alerts are completed or after all conditions have been met.

 

 

<?php
session_start(); 
include("config.php");

$sql_site = ("SELECT * FROM sit_details_tmp WHERE `email` = '{$_SESSION['EMAIL']}'");
$site_con = mysql_query($sql_site, $conn) or die(mysql_error());
$numrows = mysql_num_rows($site_con);

if($numrows > 0){
while($row = mysql_fetch_array($site_con))
{
	   $sql_ver = ("SELECT Url FROM sit_details_tmp WHERE `email` = '{$_SESSION['EMAIL']}'");
   		   $site_ver = mysql_query($sql_ver, $conn) or die(mysql_error());
	   $rs_ver = mysql_fetch_array($site_ver);
	   
do {
$validationURL="$row[url]";
$htmlString=file_get_contents($validationURL);
$var_code = "$row[ver_code]";

if (eregi($var_code, $htmlString)) {

$ver_update=("UPDATE `sit_details_tmp` SET `id_verification` = '2' WHERE `email` = '$_SESSION[email]' AND `Url` = '$row[url]'") or die(mysql_error());

if (!mysql_query($ver_update,$conn))
  {
  die('Error: ' . mysql_error());
  }

echo '<script language=javascript>';
echo 'alert("Validation code was found! \n Your site has been successfully verified.");'; 
echo '</script>';
echo '<SCRIPT language="JavaScript">
<!--
window.location="my_account.php";
//-->
</SCRIPT>
';
}else{
echo"$var_code";
echo '<script language=javascript>';
echo 'alert("Validation code was not found! \n Please make sure you have entered the correct \n validation code and that its on the index page of your site.");';
echo '</script>';
echo '<SCRIPT language="JavaScript">
<!--
window.location="my_account.php";
//-->
</SCRIPT>
';
}
}while(mysql_fetch_array($site_ver));
}
}else{
echo"User not logged.";
}
?>

 

[btherl]

You should be able to put the alerts and then the redirect all in the same javascript block.  Then it'll run in sequence (I think).  Not 100% sure, i'm not that great with javascript.  Another option is to have a delay before the redirect.