Page 1 of 1

Checking database, before updating

Posted: Sun Jul 01, 2007 1:40 pm
by divx
Anyone have any ideas why this is not works:

Im trying to check the database to see if the url exist (in the db) before writting over it

Code: Select all

mysql_connect("correctip", "correctUsename", "Correctpass") or die(mysql_error());
mysql_select_db("correcttable") or die(mysql_error());

$URLdata = mysql_query("SELECT LT_URL FROM lt_Users")or die(mysql_error()); // the querry
	  
$DUP = FALSE;      
// need to check if $_POST['LT_URLBox'] already exist, if does, dont update  ($DUP = True)  

CheckURLExists(); 
if ($DUP == FALSE){
$result48= mysql_query("UPDATE lt_Users SET LT_URL='$lturlBox' WHERE lt_Name ='$nom' AND lt_Pss='$ps'");
}


FUNCTION CheckURLExists() {
while( $URLinfo = mysql_fetch_array( $URLdata ) ){
	if (strtolower($URLinfo['LT_URL']) == strtolower($lturlBox)){$DUP = TRUE;}
	}
	Return $DUP;
	}
It seems to write over the url regardless of it already being present in the db

Posted: Sun Jul 01, 2007 2:04 pm
by califdon
Your function returns the value of the internal variable $DUP, which has no relation to the global variable $DUP. You should be setting the global variable $DUP to the value returned from your function checkURLExists(). This is a matter of "scope" of variables. In general, avoid naming global variables and local variables the same.

Posted: Sun Jul 01, 2007 2:20 pm
by divx
$DUP is defined out side of the function, so shouldnt it be global when used inside the function?

Posted: Sun Jul 01, 2007 2:42 pm
by miro_igov
May be useful to check the UNIQUE KEY and ON DUPLICATE KEY UPDATE mysql statements.

Posted: Sun Jul 01, 2007 2:47 pm
by Benjamin
I was bored. This is untested and is only meant to steer you in the right direction..

Code: Select all

<?php
mysql_connect("correctip", "correctUsename", "Correctpass") or die(mysql_error());

// you need to select the database here, not the table..
mysql_select_db("correcttable") or die(mysql_error());

// i am lazy
function esc($foo)
{
    return mysql_real_escape_string($foo);
}

// pulling all the records? - NO!
#$URLdata = mysql_query("SELECT LT_URL FROM lt_Users")or die(mysql_error()); // the querry

// I am assuming $lturlBox is already set..
$resource_id = mysql_query("SELECT count(*) as total FROM lt_Users WHERE LOWER(LT_URL) = '" . esc(strtolower($lturlBox)) . "'") or die(mysql_error());

$result = mysql_fetch_assoc($resource_id);          

if ($result['total'] < 1)
{
    // need to escape your strings..
    // and you probably only want to update 1 record, so I added a limit..
    $result48= mysql_query("UPDATE lt_Users SET LT_URL = '" . esc($lturlBox) . "' WHERE lt_Name = '" . esc($nom) . "' AND lt_Pss = '" . esc($ps) . "' LIMIT 1");
}

Posted: Sun Jul 01, 2007 3:06 pm
by divx
wow, thanks, didnt expect to get such a full answer.

(was using db not table, was jsut a mmistake in my example)

Thanks again, Ill have a look through it and try it out

Posted: Mon Jul 02, 2007 5:50 pm
by califdon
divx wrote:$DUP is defined out side of the function, so shouldnt it be global when used inside the function?
No.

There's no need to have the "outside" variable in the first place. Just change your If condition to this:

Code: Select all

If (!CheckURLExists()) {