0

i have an array called MyArray i want to insert its data into mysql but first need to check if it exist in mysql already. could any one tell me how i can loop through array and how to reference each variable inside the array then insert those data in to mysql?

$MyArray[] = array(  
        "usernameVar" => htmlspecialchars($usernameVar),
        "profPic" => htmlspecialchars($profPic), 
        "idVar" => htmlspecialchars($idVar), 
        "standardResolution" => htmlspecialchars($standardResolution),  
        "imagePageLink" => htmlspecialchars($imagePageLink),
        "createdTimeVal" => htmlspecialchars($createdTimeVal),
        "imageTags" => htmlspecialchars($imageTags),
);  

$MyArray = array_reverse( $MyArray );

//now i need to loop through array and insert non duplicate data in to mysql

$result = mysql_query("SELECT imageUrl FROM mytable WHERE imageUrl = '$standardResolution'");
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
if(mysql_num_rows($result) == 0) {
    $j++;
    echo "<a href='".$imagePageLink."'><img src='".$standardResolution."'  width='150' height='150' border='0'></a><br> ";

    // row not found, do stuff...
    echo "New Users(".$i."):";
    echo "<br>User Name(".$i."):".$usernameVar;

    $result2 =  mysql_query("INSERT INTO mytable (ID, username, profile_picture, instaId, imageUrl,imagePageURL, CreatedTime, imageTags, date) VALUES('$ID','$usernameVar','$ProfilePicVar','$idVar','$standardResolution','$imagePageLink','$createdTimeVal','$imageTags',NOW())");

    if (!$result2) {
        die('Invalid query: ' . mysql_error());
    }
} 
else 
{
    $m++;

    // do other stuff...
    echo "Already Added Users(".$i."):";
    echo "<br>User Name(".$i."):".$usernameVar;
};
kero
  • 10,647
  • 5
  • 41
  • 51
user1788736
  • 2,727
  • 20
  • 66
  • 110
  • You should use `mysql_real_escape_string()` instead of `htmlspecialchars()` because [it's not safe for MYSQL operations](http://stackoverflow.com/questions/7355639/does-htmlspecialchars-protect-this-mysql-query). –  Sep 21 '13 at 17:39
  • you means because of sql injection it is not safe to use htmlspecialchars ? – user1788736 Sep 21 '13 at 17:50
  • Yes. SQL injection is probably one of the most common website hacks out there. If you're vulnerable, someone will find out and hack you. –  Sep 21 '13 at 17:54

2 Answers2

1

See INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

djot
  • 2,952
  • 4
  • 19
  • 28
  • thanks for reply .I know the insert statement syntax but how to loop through array items and reference each item and put them as variables so i used in insert statement! – user1788736 Sep 21 '13 at 17:42
0

You can loop through an array using foreach in PHP:

foreach($MyArray as $key=>$value){
    ....
}

Inserting from the array is a little more complicated, but in principle, you loop over each variable in the array and create a string.

foreach($MyArray as $key=>$value){
    $names .= $key.",";
    $values .= "'$value',";
}
$names = substr($names, 0, strlen($names) - 1);
$values = substr($values, 0, strlen($values) - 1);

then do:

mysql_query("INSERT INTO table ($names) VALUES($values)");
Zack Newsham
  • 2,810
  • 1
  • 23
  • 43
  • Don't forget about [`implode()`](http://us1.php.net/manual/en/function.implode.php) and [`mysql_real_escape_string()`](http://us1.php.net/manual/en/mysqli.real-escape-string.php)! –  Sep 21 '13 at 17:46
  • but how to put usernameVar,profPic,Idvar,standardResolution,imagePageLink,createdTimeVal,imageTags from array as variables so i refrence them in my select and insert statements ? in your example above what is names and values means ? – user1788736 Sep 21 '13 at 17:48
  • $names and $variables are just empty strings. @MisterMelancholy I don't typically use implode, because it has to follow the same rule regardless of datatype - using the above code you can easily extend it to support numeric values without the ''. – Zack Newsham Sep 21 '13 at 17:58
  • I thought MYSQL wouldn't care if you tried to save `'3'` to an `INT` field, and just convert it to `3`? Regardless, I see your point, and it's valid. Still, there's no excuse to not use `mysql_real_escape_string()`. Even if you're using your own values from the database, you could still accidentally run some invalid SQL and the whole query would break. –  Sep 21 '13 at 18:06
  • it won't care on some cases, but I've found it a problem - particularly with timestamp values, and its most apparent when trying to order them on select after. – Zack Newsham Sep 21 '13 at 18:12
  • i just did like this :foreach( $myArray as $ArrayResult ) { $usernameArrayVar = $ArrayResult['username']; }. Much easier and easy to reference the array items! – user1788736 Sep 21 '13 at 18:53
  • I think maybe I misunderstood what you were trying to do. If you've worked it out, post your own answer. – Zack Newsham Sep 21 '13 at 18:54