www.webdeveloper.com
Results 1 to 3 of 3

Thread: insert or update / and ignore

  1. #1
    Join Date
    Dec 2007
    Posts
    61

    Question insert or update / and ignore

    Hello.

    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??

  2. #2
    Join Date
    Dec 2007
    Posts
    61

    Question

    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?

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,175
    For your first question, see the ON DUPLICATE KEY syntax for INSERT; or a simpler but less portable solution is the REPLACE INTO query syntax.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles