[RESOLVED] insert / update / ignore

[knightman]

Hello.


** i postet this on the php forum, but i think it should be here on SQL **


let's say i want to add a row to a table called "todaypics" with this 3 columns:
| picnum | imagename | todayhits |

and i do this:

PHP Code:

mysql_query("INSERT INTO todaypics (picnum, imagename, todayhits) VALUES('72', 'somepicturename.jpg', '35') ")
or die(mysql_error());


the data is inserted
| 72 | somepicturename.jpg | 35 |
witn no problem.

but if the row was already on the
tablewith this data:...
| 72 | somepicturename.jpg | 34 |

...how sohuld be the query to avoid the duplicate error and update the column "todayhits" value to 35??

------------------------------------------------------------------


other situation i have:

on the other table named "pics", i have hundreds of rows.
the columns are:
| picnum | imagename | hits |

if i want to display the 1st 50 images sort by hits i do this:

PHP Code:



$result = mysql_query("SELECT * FROM pics ORDER BY hits desc LIMIT 50")
or die(mysql_error());  
echo "<table border='1'>";
echo "<tr> <th> PicNum </th> <th> Picture </th> <th> Views </th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['pid'];
    echo "</td><td>";
    echo "<a href=\"/image-path/".$row['imagename']."\" target=\"_blank\"><img src=\"/thumbnail-path/".$row['imagename']."\" /><a/>";
    echo "</td><td>";
    echo $row['hits'];

    echo "</td></tr>";
}
echo "</table>";


if 50 images are to be displayed, but only 25 has some hits, the rest of the images are ordered by picnum..

is there a way to ignore the rows when the "hits" value is zero?

[yamaharuss]

If NOT EXISTS (Select imagename from todaypics where imagename = 'somepicturename.jpg')  INSERT INTO todaypics (picnum, imagename, todayhits) VALUES('72', 'somepicturename.jpg', '35')

SELECT TOP 50 * FROM pics ORDER BY hits desc

[knightman]

Thanks yamaharuss...
i'll try it tonight and will let you know.

[ssystems]

Originally Posted by yamaharuss

SELECT TOP 50 * FROM pics ORDER BY hits desc

Keep in mind you're using MySQL and to ignore the pics w/ 0 hits

SELECT * FROM pics WHERE hits > 0 ORDER BY hits desc LIMIT 50

[knightman]

yamaharuss:
i tried this:

PHP Code:

If NOT EXISTS (Select imagename from todaypics where imagename = 'somepicturename.jpg')  INSERT INTO todaypics (picnum, imagename, todayhits) VALUES('72', 'somepicturename.jpg', '35')


but didn't work. so after some googling, found this:

PHP Code:

mysql_query("INSERT INTO todaypics (picnum, imagename, todayhits)
Select Distinct 72, somepicturename.jpg, 35
(Select * From todaypics Where picnum == 72")
or die(mysql_error());


this one works great!
-----------------------------------------------

ssystems:

PHP Code:

SELECT * FROM pics WHERE hits > 0 ORDER BY hits desc LIMIT 50


was displaying the files with 0 hits, but i found this:

PHP Code:

SELECT * FROM pics WHERE trim(hits) != '0' ORDER BY hits desc LIMIT 50


this work ok!

thank you both...

KnightMan

[ssystems]

I see your hits column was a varchar