Checking database, before updating

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
divx
Forum Newbie
Posts: 20
Joined: Sun Jul 01, 2007 1:37 pm

Checking database, before updating

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
divx
Forum Newbie
Posts: 20
Joined: Sun Jul 01, 2007 1:37 pm

Post by divx »

$DUP is defined out side of the function, so shouldnt it be global when used inside the function?
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Post by miro_igov »

May be useful to check the UNIQUE KEY and ON DUPLICATE KEY UPDATE mysql statements.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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");
}
divx
Forum Newbie
Posts: 20
Joined: Sun Jul 01, 2007 1:37 pm

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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()) {
Post Reply