[SOLVED] SELECT and UPDATE queries

[DasHaas]

I have the following code that displays an ad at the top of my webpage. I would like to add an update statement that updates a field in my table for the cooresponding ad whenever the ad is displayed. Here is what I have so far:

 

<?php
// includes
include('includes/PXS_Conf.php');

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ('Unable to connect to MySql server!');

// select database
mysql_select_db($db) or die ('Unable to select database!');

// generate and execute query
$query = "SELECT id,HREF, SRC, ALT FROM PXS_Ads WHERE status = 1 and type =1 and impressions_left >0 ORDER BY RAND() LIMIT 1";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
while ($row = mysql_fetch_object($result))
{
    $HREF = "$row->HREF";
    $SRC = "$row->SRC";
    $ALT = "$row->ALT";

?>
      <table width="729" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td scope="col"><a href='<?php echo($HREF); ?>'><img src='<?php echo($SRC); ?>' name='<?php echo($ALT); ?>' border=0></a></td>
	<?php
}
//close connection
mysql_close($connection);
?>

 

This works fine but I also want to update the impressions_left to impressions_left-1, so when the impressions_left field = 0 the ad will no longer be displayed

[a1phanumeric]

Hi DasHaas,

 

Why don't you add the following just before you close your MySQL connection:

 

<?php
// includes
include('includes/PXS_Conf.php');

// open database connection
$connection = mysql_connect($host, $user, $pass) or die ('Unable to connect to MySql server!');

// select database
mysql_select_db($db) or die ('Unable to select database!');

// generate and execute query

// NEW CODE PART 1 //
// I changed the mysql query to
// also retrieve the impressions_left field data

$query = "SELECT id,HREF, SRC, ALT, impressions_left FROM PXS_Ads WHERE status = 1 and type =1 and impressions_left >0 ORDER BY RAND() LIMIT 1";

// END NEW CODE PART 1 //

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
while ($row = mysql_fetch_object($result))
{
    $HREF = "$row->HREF";
    $SRC = "$row->SRC";
    $ALT = "$row->ALT";

    // NEW CODE PART 2 //
    // Grab some extra data from the DB

    $ID = "$row->id";
    $IMPRESSIONSLEFT = "$row->impressions_left";

    // END CODE PART 2 //

?>
      <table width="729" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td scope="col"><a href='<?php echo($HREF); ?>'><img src='<?php echo($SRC); ?>' name='<?php echo($ALT); ?>' border=0></a></td>
	<?php
}

// NEW CODE PART 3 //
// Decrease the impressions left by one...

$IMPRESSIONSLEFT--;

// ... and update the DB

$query = "UPDATE PXS_Ads SET impressions_left = $IMPRESSIONSLEFT WHERE id = $ID";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

// END NEW CODE PART 3 //


//close connection
mysql_close($connection);
?>

 

Hope this helps!

 

Ed.

[DasHaas]

your a genius, it worked like a champ!!! thanks a million. Is it possible to update the table whenever someone clicks on the image

[a1phanumeric]

No worries

 

Yes, it's possible to have the database update when someone clicks on the image.

 

- My personal preference would probably be an AJAX call (using Prototype) to POST some data to a PHP page which can run server-side code to update the database. After that ajax call, you can then redirect to user to wherever the image link goes to.

 

- The other method would be to create a 'bounce page', whereby it'd work like this:

 

1. Adjust the code that 'echoes' out the image to change the link to 'www.YOURSITE.com/bounce.php?id=6' - where the link always refers to your 'bounce page', and it also builds a query string with the images' ID.

2. Create a bounce page that will read in the ID passed to it, then read from the database the image relating to that ID, as well as the link that the user should be forwarded to.

3. Update the record in the database, also based on the ID sent by the previous page.

4. Then redirect the user to the page you obtained in step 2.

 

Hope that makes sense!

 

Ed.

[DasHaas]

you lost me but, I'll study up on it lol. Thanks again for the help!