[SOLVED] SQL, checker, update or insert

[czzplnm]

So grabs a myspace image, then cleans the url a little more. Then supposed to check if the "ID" exists already then if it doesnt exist its supposed to insert. Not working though, Tried many ways to update then insert if doesnt exist etc.

<?php
$db_database = "dbtable";
$db_host = "localhost"; 
$db_user = "username"; 
$db_pass = "password";

	mysql_connect($db_host, $db_user, $db_pass) 
		or die ("Unable to connect to database.");
	mysql_select_db($db_database) 
		or die ("Unable to select database.");

		$loginid = $_POST['id'];
		if($fp = fopen("http://www.myspace.com/$loginid", 'r')) {
			while (!feof($fp)) {
				$info .= fread($fp, 8192);
			}
			fclose($fp);
preg_match('/src="(.*)images.myspacecdn.com(.*)(jpg|png|gif|bmp)"/', $info, $image);
$table = $image[0];
preg_match('/"(.*)images.myspacecdn.com(.*)(jpg|png|gif|bmp)"/', $table, $image2);
$table2 = $image2[0];
$ctime = Time();
if ($table2 != '') {
echo $table2;
      $sql = "SELECT myid FROM addtrain WHERE myid=\"$loginid\"";
$query = mysql_query($sql);

if(mysql_num_rows($query) > 0) {
$insert = "UPDATE `addtrain` SET `time` = '".$ctime."' WHERE `myid` = '".$loginid."'";
mysql_query($insert)   or die(mysql_error());
      } 
      else 
    {
        $insert2 = "INSERT INTO addtrain(myid, time, imgurl, etc) VALUES (".$_POST['id'].", ".$ctime.", ".$table2.", 0)"; 
mysql_query($insert2) or die(mysql_error());
}
}
}

?>

 

 

This little codes been working on and off, but sometimes it wont update, sometimes it doesnt insert. I do not know what to try now. Any help would be great.

[.josh]

i'd say your culprit is your preg_matches.  You are using a greedy dot matches, so more than likely you are matching a whole lot more than you are wanting to match, probably including random quotes, which would cause your query to mess up.  They can be written much better, but at the very least, change all your (.*) to (.*?)

[czzplnm]

I have it echoing the preg_match result and that part is perfect no problems. My problem is the update SQL if exists, then insert if doesnt exist. Also preventing the query code if their is no image returned correctly.

[.josh]

okay so let's assume your preg_matches are right. where is $loginid assigned?

[czzplnm]

I just have it assigned at the top of the page $loginid = $_POST['id'] ,

maybe need to  do something like this,

if ($_POST['Submit']=='Login')

{

*CODE ABOVE*

}

 

 

 

 

This is HTML form im using if any help.

 

<form method="post" name="Grab" action="test2.php">

<input type="text" name="id" value="Myspace ID" size="20">

<input type="submit" value="Join Train!">

</form>

[Philip]

You could just use INSERT ON DUPLICATE

[czzplnm]

Yeah I was looking at that last night, doesnt that require more database fields and additional processing?

[Philip]

It would actually require less processing than what you're using right now. Something like the following (untested of course), as long as myid is an unique key column:

$sql = "INSERT INTO addtrain(myid, time, imgurl, etc) VALUES ('".$_POST['id']."', '".$ctime."', '".$table2."', 0) ON DUPLICATE KEY UPDATE time = '".$ctime."'";

 

And you should properly sanitize your inputs (the above query is not sanitized.)

[czzplnm]

The $sql code is executed and everything is storing nicely, but still duplicate fields of $_POST['id'], its inserting multiple of same still. The myid is supposed to be unique and in only 1 allowed. But, it allows the same. I can get 1 to work UPDATE, or the other INSERT, but cant UPDATE, if 0 affected INSERT or something?

[Philip]

Does column 'myid' have a unique property (check in PhpMyAdmin, or the like) & when you echo out $sql - does it show everything correctly?

[czzplnm]

Does column 'myid' have a unique property (check in PhpMyAdmin, or the like) & when you echo out $sql - does it show everything correctly?

How to set unique property using, phpmyadmin in cpanel etc. Ill check phpmyadmin see if its easy to find unique property. But my $sql result is.

INSERT INTO addtrain(myid, time, imgurl, etc) VALUES ('470122370', '1254789292', '"http://c4.ac-images.myspacecdn.com/images02/88/m_bdffe541246e42f8b9ef00c84c97be3f.jpg"', 0) ON DUPLICATE KEY UPDATE time = '1254789292'

 

 

 

 

*UPDATE*

found unique stuff, but cant since duplicates. Need to run a query to delete duplicates i guess

[Philip]

Yup

[czzplnm]

How would i go about deleting duplicates?

 

*UPDATE* I think i could do this query in a delete.php file

 

ALTER TABLE `addtrain` ADD UNIQUE (

`myid`

)

 

Then when it detects problem the output should be this format below. Just preg_match the numbers

and form a delete query with it and execute?

 

MySQL said: Documentation

#1062 - Duplicate entry '467764824' for key 'myid'

[Philip]

change that query to

ALTER IGNORE TABLE `addtrain` ADD UNIQUE (`myid`)

[czzplnm]

Returns with value 1 now. This what I have. Query was output correctly the above way, but cant

preg_match the numbers outputed, nothings being outputed.

$sql = "ALTER IGNORE TABLE `addtrain` ADD UNIQUE (`myid`)";
$query = mysql_query($sql) or die(mysql_error());
echo $query;

preg_match('/[0-9]{4,14}/', $query, $myid);
$numb = $myid[0];
echo $numb ;

[Philip]

Running that query once will alter the table to delete duplicates and add a unique property to the column. You only need to run it once, not every time. Since you ran it, you can now run the UPDATE ON DUPLICATE query each time.

[czzplnm]

Perfect mate, thanks for all the help, everything is in line only thing im stuck on. I want to check

$_POST['id'] if it is numbers or letters. I dont want people using their myspace "Name" example: www.myspace.com/ashtons but their numbers myspace.com/12893733 I got something like this,

Should I preg_match $loginid to see if it fits? $loginidpr can be the preg_match'd # if it went correctly etc

if ($table2 != '' && $loginidpr == $loginid) {

 

$sql = "INSERT INTO addtrain(myid, time, imgurl, etc) VALUES ('".$_POST['id']."', '".$ctime."', '".$table2."', 0) ON DUPLICATE KEY UPDATE time = '".$ctime."'";

$query = mysql_query($sql);

echo $sql;

}

}

[.josh]

ctype_digit

 

or

 

if you really wanna preg_match it,

 

'~^[0-9]+$~'

[czzplnm]

Beautiful, now gonna add a timer to check the db time, thanks mate . Really appreciate the quick help. Prolly gonna stick around here for a while