MySQL Queries

[jakeruston]

Right, I'm using a MySQL Query to select all the rows from a table. I'm also using mysql_num_rows to count how many rows there is. I'm also using - while ($sites=mysql_fetch_row($query)){ - which should put the rows into an array. How can I use PHP to check if the end of the array has been reached in a while loop or if the row number being checked is equal to the number of rows in total?

[GingerRobot]

Use a counter? Start it at 1 before the loop, increase it by 1 each time the loop runs. Check to see if it's equal to the number of rows. If it is, do what you want.

 

Although, couldn't you just do whatever you want to do after the loop has run?

[waynewex]

Maybe sizeof() might help?

[jakeruston]

I'm trying to use this code so far then:

 

$counter=1;

$query=mysql_query("SELECT * FROM sites");

$numrows=mysql_num_rows($query);

while ($sites=mysql_fetch_row($query)){

}

while ($sites[$counter] != $numrows) {

$impressionsq = mysql_query("SELECT impressions FROM sites WHERE url='" . $sites[$counter] . "'");

....

....

....

$result = mysql_query("UPDATE sites SET weight=" . $weight . " WHERE `url`='" . $sites[$counter] . "';");

$counter += 1;

}

 

Although there are only 3 rows, the page looks like it isn't going to load...

[GingerRobot]

Little confused by your code. What are you trying to do?

 

I think you want an if statement rather than a where statement here though:

 

while ($sites[$counter] != $numrows)

 

I also imagine it's supposed to be $counter and not $sites[$counter]

[craygo]

You seem to be complicating things here.

 

Are you trying to update the weight field for all your rows??

[jakeruston]

That line is supposed to be a while because I want it to do the code while the number in the array isn't last one.

 

Yeah, I want to update the weight field for all of the rows, but the weight field will be different for each row.

[craygo]

Where is the weight value coming from??

 

Also where is the url value coming from?? It looks like you are comparing the url to each field in each row that is returned.

 

Ray

[jakeruston]

Its based on a few variables in the middle of the while loop.

[craygo]

I edited my post above but to help out more you may want to show your table data and let us know what you want to accomplish. Or should I say what your final result should be.

 

Ray

[jakeruston]

Right, what the script is supposed to do is it gets all of the sites from the sites table, puts them all into an array, and then updates all of their "weight" fields using this formula:

 

$weight = ceil($dmozbonus*$indexed*($impressions*$siteprbonus*5996)/(750000 - $impressions));

 

It all works fine when I've set it to do it for 1 site in the table based on a GET variable, I'm just trying to make it update all of them at once in one script automatically, so I need it to keep going along each site in the array and updating it, and then moving on to the next one until all of them have been updated.

[GingerRobot]

And where to all of these other variables come from? Wouldn't it be better to do this with one query to the database and update all the rows at once?

[jakeruston]

The other variables are dependant on the site, these variables come also from the database.

[GingerRobot]

Can you give us your database structure? And tell us which fields correpond to which of the above variables? You should just be able to do this one update query.

[jakeruston]

--

-- Table structure for table `sites`

--

 

CREATE TABLE IF NOT EXISTS `sites` (

  `url` varchar(50) NOT NULL,

  `user` varchar(20) NOT NULL,

  `impressions` varchar(100) NOT NULL,

  `weight` varchar(25) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `sites`

--

 

INSERT INTO `sites` (`url`, `user`, `impressions`, `weight`) VALUES

('www.milofi.com', 'blah3', '50000', '9594'),

('www.hello.com', 'blah2', '5', '0'),

('www.google.com', 'blah1', '5', '0');

 

The impressions field is the $impressions variable in the formula, the other variables come from data collected through the Yahoo API.

[craygo]

Try this out

 

<?php
$query = "SELECT `impressions`, `url` FROM `sites`";
$result = mysql_query($query) or die(mysql_error());
while ($sites = mysql_fetch_assoc($result)){
$url = $sites['url'];
$impessions = $sites['impressions'];
$weight = ceil($dmozbonus*$indexed*($impressions*$siteprbonus*5996)/(750000 - $impressions));
$update = "UPDATE `sites` SET `weight` = '$weight' WHERE `url` = '$url'";
mysql_query($update) or die(mysql_error());
}
?>

 

Ray

[jakeruston]

Right, that seems to work a lot better now.

 

However, it seems VERY slow, even with only 3 rows. It took like 10 - 15 minutes. Any way I can speed it up?

[craygo]

the query is very simple and should not take any time at all, I would think it is the API's that are taking time to retrieve the information.

[jakeruston]

Do you think it would be a good idea to have separate scripts which updates from those API's and then puts the information into the Database, and then this script just simply gathers them from the database? That way it could have sort-of a "cache" effect.

[craygo]

Probably your best bet. Not sure how often the API information changes. You could set up a script to run from a cron job to update the database once a day or something. Then querying the database would take milliseconds.

 

Ray

[jakeruston]

Right, one last question:

 

What PHP could I use to get the number of pages indexed on Google?

[jakeruston]

Forget that last question, I have a more important one.

 

Now that it all updates fine, I want to add more to the loop.

 

I use this code:

 

$query = "SELECT 'weight' FROM `sites` WHERE user='$user'";

$result = mysql_query($query) or die(mysql_error());

$weightno = mysql_num_rows($result);

while ($sites = mysql_fetch_assoc($result)){

$weight = $sites['weight'];

while ($sites['weight'] !== $weightno) {

$totalweight += $weight;

}

$query = "UPDATE 'user' SET weight='$totalweight' WHERE user='$user'";

$result = mysql_query($query) or die(mysql_error());

}

 

This is in loop at the end. I use this code because now I want to also select all of the sites associated with a certain username and then add all of their weights together and put it into one variable to add that into another field in another table. The only problem with this one is that it doesn't actually update the database, and also, it stops after the first of the first loop, so it actually stops the first loop we were working on.

 

Any ideas?

[craygo]

There is no need to count rows to get the loop to end. The while statement will only run as long as rows are being returned. It will stop on it's own when the end is reached.

 

Also remember that field names and table names should be in backticks (`) and field values should be in single quotes (').

 

<?php
$totalweight = 0;
$query = "SELECT `weight` FROM `sites` WHERE `user` = '$user'";
$result = mysql_query($query) or die(mysql_error());
while ($sites = mysql_fetch_assoc($result)){
$totalweight += $sites['weight'];
}
$query = "UPDATE `user` SET `weight` = '$totalweight' WHERE `user` = '$user'";
$result = mysql_query($query) or die(mysql_error());

?>

 

Ray

[jakeruston]

Thanks for your help, I managed to figure it out

 

The only thing I need to find is how to get the number of pages indexed by Google.