SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Quick Question - Won't update my database

    Hi all

    Recent had a thread which works great, but I've added a new column to my photos table called 'views' no errors showing but can't seem to figure out why it won't update the column??

    PHP Code:
    $sql "SELECT title, DATE_FORMAT(gallery.date, '%M %D %Y') AS dr, caption
        FROM photos
        LEFT JOIN gallery
        ON gallery.date = photos.date
        WHERE photo_id = "
    .((int)$_GET['photo_id']);
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        
    $row mysql_fetch_array($result);
        
    #New piece of code
    $update mysql_query("UPDATE photos SET views = views+1 WHERE photo_id = '".mysql_real_escape_string($photo_id)."'") or die(mysql_error()); 
    Thanks

  2. #2
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Without seeing the error my guess would be you need to use this:

    PHP Code:
    $update mysql_query("UPDATE photos SET views = views+1 WHERE photo_id = '".mysql_real_escape_string($_GET['photo_id'])."'") or die(mysql_error()); 
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  3. #3
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    photo_id is an integer, isn't it? Then do for your UPDATE what you did for your SELECT:
    PHP Code:
    $update mysql_query("UPDATE photos SET views = views+1 WHERE photo_id = ".((int)$_GET['photo_id'])) or die(mysql_error()); 
    Numerical values are never quoted, and do not be escaped like string do (provided you are adequately enforcing the datatype).
    PHP questions? RTFM
    MySQL questions? RTFM

  4. #4
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you haven't declared a value for "photo_id" yet.

    Normally if a query does not do what I expect it to do, I echo it to the page and make sure that it is formated and outputs what I was looking for. If it looks ok and I have a syntax error, I usually find out by trying to run the query through PHPMyAdmin and then see what error is returned.

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should of recognized this myself, thanks for putting this out guys.

    Just to make this clear, there was no error given, it just didn't update the column values in my table, thanks!

    Jeanco - example works
    Kromey - example also works - which I think works best

    photo_id is an integer, isn't it?
    yes kromey like the previous thread, but Ive come to the point where I need to select the 220607/ with the photo id, meaning it will now have a trailing slash 220607/001 in the column, so will I have to change the 'type' or is there an easier way?

    Example:

    PHP Code:
    #Different snippet from related page
    $sql "SELECT photo_id, views, caption FROM photos WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        for (
    $i 0$row mysql_fetch_array ($result); $i++) {
        if((
    $i != 0) and (($i % 4) == 0))
        
    echo'<p></p>';
        echo 
    '<a href="bigimage/' $row['photo_id'] . '"><img src="thumbs/220607/' $row['photo_id'] . '.jpg" title="' $row['views'] . '" class="padright"></a>';
        } 
    but I need a way of selecting the right thumbs/ ??? /. $row['photo_id'] - other wise it will always look in that folder for the thumbs even though it's from a different date?

    PHP Code:
    echo '<img src="thumbs/220607/' $row['photo_id'] . '.jpg" title="' $row['views'] . '"> 
    Hope am making sense, Thanks

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it just a matter of:

    PHP Code:
    echo '<a href="bigimage/' $row['photo_id'] . '"><img src="thumbs/' $row['folder'] . '/'$row['photo_id'] . '.jpg" title="' $row['views'] . '" class="padright"></a>';
        } 
    So I just create another column and select it this way?

    Thanks

  7. #7
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where is the 220607/ coming from? Is that your date? If it is, then just make sure you select your date as well and format it as you need it.

    Assuming it is your date, it appears to be in DDMMYY format, which can be easily achieved in MySQL thusly:
    Code:
    SELECT DATE_FORMAT(date, '&#37;d%m%y') AS subdir
    Simple rule of thumb: Never duplicate data in your database. If you need something that is based on other data, then use that other data and don't try to duplicate it elsewhere.
    PHP questions? RTFM
    MySQL questions? RTFM

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    220607/ is the date the images where taken, so all the thumbs I select will be from a separate date and when clicked will update the views like i mentioned before.

    Simple rule of thumb: Never duplicate data in your database. If you need something that is based on other data, then use that other data and don't try to duplicate it elsewhere.
    I see, so really:
    PHP Code:
    echo '<a href="bigimage/' $row['photo_id'] . '"><img src="thumbs/' $row['subdir'] . '/'$row['photo_id'] . '.jpg" title="' $row['views'] . '" class="padright"></a>'; } 
    Should work fine? And just change the DATE format?

    Thanks

  9. #9
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems to work ok:

    Does it look ok to you?

    PHP Code:
    $sql "SELECT photo_id, DATE_FORMAT(date, '%d%m%y') AS subdir, views, caption FROM photos WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        for (
    $i 0$row mysql_fetch_array ($result); $i++) {
        if((
    $i != 0) and (($i 4) == 0))
        echo
    '<p></p>';
        echo 
    '<a href="bigimage/' $row['photo_id'] . '"><img src="thumbs/' $row['subdir'] . '/'$row['photo_id'] . '.jpg" title="Views: ' $row['views'] . '" class="padright"></a>';
        } 

  10. #10
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to display the date as you are selecting it for the dr aliased column (which I assume you do want it, otherwise why are you selecting it??), then don't replace it with my suggestion, just add mine in (you can select the same column multiple times without issue, don't worry about that).
    PHP Code:
    $sql "SELECT title, DATE_FORMAT(gallery.date, '%M %D %Y') AS dr, DATE_FORMAT(date, '%d%m%y') AS subdir, caption
        FROM photos
        LEFT JOIN gallery
        ON gallery.date = photos.date
        WHERE photo_id = "
    .((int)$_GET['photo_id']); 
    PHP questions? RTFM
    MySQL questions? RTFM

  11. #11
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by computerbarry View Post
    Seems to work ok:

    Does it look ok to you?

    PHP Code:
    $sql "SELECT photo_id, DATE_FORMAT(date, '%d%m%y') AS subdir, views, caption FROM photos WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
        
    $result = @mysql_query($sql) or die('Error: ' mysql_error());
        for (
    $i 0$row mysql_fetch_array ($result); $i++) {
        if((
    $i != 0) and (($i 4) == 0))
        echo
    '<p></p>';
        echo 
    '<a href="bigimage/' $row['photo_id'] . '"><img src="thumbs/' $row['subdir'] . '/'$row['photo_id'] . '.jpg" title="Views: ' $row['views'] . '" class="padright"></a>';
        } 
    Yup, looks great. I think I misunderstood the situation, so you can probably safely ignore my previous post.
    PHP questions? RTFM
    MySQL questions? RTFM

  12. #12
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Kromey just posted at the same time there..

    (you can select the same column multiple times without issue, don't worry about that).
    I see what you mean, important point taken there, Cheers

    Just to make it clear, do you mean I can select any column multiple times and give it different names and use them on my page in different ways?

    Thanks

  13. #13
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    New problem - date related, which basically selects all the dates from my table

    PHP Code:
    $query "SELECT date FROM gallery";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"photos/{$row['date']}\">{$row['date']}</a><br>\n"
    I used DATE_FORMAT(date, '&#37;d%m%y') AS dr2 or whatever format to echo the date but when I click the link, it has no address[value]

    Example:

    PHP Code:
    $query "SELECT DATE_FORMAT(date, '%d%m%y') AS dr2 FROM gallery";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"photos/{$row['date']}\">{$row['dr2']}</a><br>\n"

    Cheers Kromey

  14. #14
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's because $row['date'] doesn't exist - you have only a single column, dr2, in that SQL statement. var_dump($row) to see what I mean.
    PHP questions? RTFM
    MySQL questions? RTFM

  15. #15
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks kromey, thanks to your example Ive got it working, I selected the date twice and used it in different ways, which works fine now:

    Does this look right?

    PHP Code:
    $query "SELECT DATE_FORMAT(date, '%D %M %Y') AS dr2, date FROM gallery";
        
    $res mysql_query($query);
        while(
    $row mysql_fetch_assoc($res))
        echo 
    "<a href=\"photos/{$row['date']}\">{$row['dr2']}</a><br>\n"
    Thanks

  16. #16
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On a different note. We spoke about including my php code above my html like shown below, from a previous thread, then echo the table columns anywhere on the page.
    PHP Code:
    <?php
    require_once('includes/mysql_connect.inc.php');
    $sql "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    ?>
    <html>
    <head>
    <title><?php echo $row['title'?></title>
    </head>
    <body>
    <?php /*echo what you need here */ ?>
    <b>More HTML code, or whatever</b>
    <?php /*you can break up your PHP as much as your need to this way*/ ?>
    </body>
    </html>
    I managed to get everything working fine in the above, until i added another query, like below, which then seemed to cancel out any values for my first query, why is that?

    PHP Code:
    <?php
    /*set up database connection here*/
    require_once('includes/mysql_connect.inc.php');
    $sql "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    ?>
    <html>
    <head>
    <title><?php echo $row['title'?></title>
    </head>
    <body>
    <?php /*echo what you need here */ ?>
    <b>More HTML code, or whatever</b>
    <?php
    /*different query all together*/
    $query "SELECT DATE_FORMAT(date, '%D %M %Y') AS dr2, date FROM gallery";
    $res mysql_query($query);
    while(
    $row mysql_fetch_assoc($res))
    echo 
    "<a href=\"photos/{$row['date']}\">{$row['dr2']}</a><br>\n"
    ?>
    <?php
    /*the echo below, which is suppose to be from my first query doesn't work ?*/?>
    <h1><?php echo $row['date'?></h1>
    </body>
    </html>
    So the first query columns [title], [date] etc didn't work once it was below the second query?

    Thanks

  17. #17
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's because you're overwriting your variables when you're running the new query. There's nothing magical about variables storing results from a database query, they can be overwritten just like any other variable.
    PHP Code:
    $var "Hello";
    echo 
    $var//outputs "Hello"
    $var "World";
    echo 
    $var//outputs "World" 
    The fix is quite simple: use different variables names for the second query.
    PHP questions? RTFM
    MySQL questions? RTFM

  18. #18
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see. Thanks for clearing that up.

    Ok the new problem, if you read above with two querys, which is selecting the same table and columns but I need while($row = mysql_fetch_assoc($res)) in my second query. But for getting confused reasons we'll just pretend that the second query is selecting a different table with a while($row =

    why won't it show my columns from the first query below the second??

    Thank You

  19. #19
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I addressed just this in my previous post - you are overwriting the variable $row and then expecting a previous value to be there. It's not, because you're while loop overwrote it with the results of the second query. This is why the second query appears to be "canceling out" the first one. The fix, as I've already stated, is to use different variable names.
    PHP questions? RTFM
    MySQL questions? RTFM

  20. #20
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks again Kromey, thanks for your time

    i just thought you only needed to name example $row, to $row1 if they where inside the same query <?php ?> tag

    Thanks

    One last thing:

    Are you saying this is possible with one query, and just add more DATE stuff? Or something like this?

    Or just keep it like before with two separate query's, and re-name the variables?

    PHP Code:
    require_once('includes/mysql_connect.inc.php');
        
    $sql "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery WHERE date='" mysql_real_escape_string($_GET['date']) . "'";
        
    $result mysql_query($sql);
        
    $row mysql_fetch_array($result);
        
    $res mysql_query($sql);
        while(
    $row1 mysql_fetch_assoc($res)) 
    Big Thank You


Bookmarks

Posting Permissions

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