Page 1 of 1

skip missing rows

Posted: Wed Feb 23, 2005 12:08 pm
by alix
I have a mysql db that has a table of links i wrote a script that pulls the links in order by id number. The id numbers are like 1,2,3,4... however i want to make it so that if i delete a row from the database ane make it like 1,3,4 it wont error out when it reaches row 2. How could i go about making something that will check to see if the row is there if not move to the next and so forth?

Here is the code im working with now to give you an idea

Code: Select all

<? 

include_once ("config.php"); 

//Find current position in rotator
$result = mysql_query("SELECT * FROM position WHERE num = '1' ");
while($myrow = mysql_fetch_assoc($result))
             &#123;

               //now print the results:
              $position= $myrow&#1111;'position'];
              $total_count= $myrow&#1111;'count'];
              $total_links= $myrow&#1111;'total_links'];
   &#125;


//determine wether or not to start rotator over
if ($position > $total_links)&#123;

    $query = "UPDATE `position` SET `position` = '1' WHERE `num` = '1' ";
	$result = mysql_query($query) or die("error: " . mysql_error()); 
	$position = "1";  
	
   &#125; else &#123;
   &#125;;
   
   //pull link from database
$result = mysql_query("SELECT * FROM links WHERE id = "$position" ");
while($myrow = mysql_fetch_assoc($result))
             &#123;

               //now print the results:
   $id= $myrow&#1111;'id'];
   $url= $myrow&#1111;'url'];
   $count= $myrow&#1111;'count'];
   &#125;
		
// record hit to url 
   $new_hit = $count+1; 
   $query = "UPDATE `links` SET `count` = $new_hit WHERE id = "$position" ";
   $result = mysql_query($query) or die ("error: " . mysql_error());

// record hit to total hits in position table  
   $new_total = $total_count+1; 
   $query = "UPDATE `position` SET `count` = $new_total ";
   $result = mysql_query($query) or die ("error: " . mysql_error());
   	 
// add to position number so that it will rotate to the next url 
   $new_position = $position+1; 
   $query = "UPDATE `position` SET `position` = $new_position ";
   $result = mysql_query($query) or die ("error: " . mysql_error());

// Display url

 ?>
<html>
<head>
<meta http-equiv="refresh" content="0;url=<? echo $url; ?>"> 
</head>
   
</html>

Posted: Wed Feb 23, 2005 12:26 pm
by timvw
however i want to make it so that if i delete a row from the database ane make it like 1,3,4 it wont error out when it reaches row 2
What would make it error out? With the while ($row = mysql_fetch_assoc($result)) loop you have shown it's even harder to imagine how you would encounter an error.... Thus: There is no need to update the id's. Each row has it's own unique id, and those id's can't be changed (because then that row wouldn't be that unique row anymore)


Another remark: you are looping over all the rows, and update them one-by-one. If you write your queries properly there is no need to have that much queries. Btw, i believe all those queries are part of one transaction. Have you considered what is going to happen if 2 people run that script at the same time? I'm pretty sure it is not going to do what you want. So pls, read your rdbms manual on how transactions work...