Jeffro

Worked like a charm. Thank you!

How would I write this in php?: If $url does not begin with 'http://' $url = '' I want to assign nothing to the variable if it doesn't begin with http:// Thanks!

Hmmm... seems like that should have worked, but it didn't seem to do anything. When the row doesn't exist, it still won't assign a zero. Here's my full code: $query = "SELECT SUBSTR(url,8,INSTR(url,'.')- as cityname, COUNT(*) as citycount FROM cities GROUP BY SUBSTR(url,8,INSTR(url,'.')-"; $result = mysql_query($query) or die(mysql_error()); $data = array(); while($row = mysql_fetch_array($result)) { $data[$row['cityname']] = $row['citycount']; } Later on, I'm calling the city names in my hyperlinks, like so: <a href="/city/ausin/">Austin</a> (<?php echo $data['austin']; ?>)<br> <a href="/city/dallas/">Dallas</a> (<?php echo $data['dallas']; ?>)<br> <a href="/city/miami/">Miami</a> (<?php echo $data['miami']; ?>)<br> This produces a result set such as: Austin (9) Dallas (4) Miami () <-- where () should read (0)

I'm printing some mysql results to a table and sometimes I have rows with no results so they never get counted... which results in an ugly looking parenthesis on my page with nothing it (). For these times, I'd like to print a zero. How can I rewrite the following to achieve this? if($row['mycount'] does not exist) { $row['mycount'] = '0'; }

No.. I have them all scheduled 10 minutes apart all night long. The duration of the query happens even when running the query right in MySQL, but.. adding index seems to have helped a lot with the smaller databases I checked. I'll know more soon! I'm going to make a really big db tonight.

Looks to me like you're trying to do the same thing I was a couple months. Check this thread and see if it provides the solution for you as it did for me.

I didn't realize his syntax would also show that. I did run it and it show myisam. I've been adding the indexes to all the databases for the last 45 mins.. it does seem to make the query run much faster. I'll create a local test environment tonight and add a few hundred thousand records to really test. Thanks for the tip on mysql_affected_rows. This sounds like a great way to control processor usage so I'm going to look hard into this as well.

I think you may be absolutely right about dedicated hosting. I was actually out looking just now. Pricey stuff! Any recommendations? Great info on the myisam/innodb! I had no idea! I'll look into how to check this next. -- Any idea how I would specify a limit on the delete statement above? To me, that sounds like a superb workaround while I switch hosts. Thanks a bunch.

Just did an index check (SHOW INDEX FROM MYTABLE) and found there is no index.

1) Good point on the 'Order by'. I'll remove that. 2) I run the query once a night during a scheduled cron job. New records are inserted via a separate cron job (not causing any issues) and old records deleted with this code. 3) Depending on the database, anywhere from 500 (these databases seem to have no problem) to 10,000 records (for my 100,000 + records database). 4) My 'Date' column has a type of timestamp 5) Not sure about the date column being indexed (I didn't write this). How can I check and should I do this?

So I went to one of my websites today and found a message that my webhost had disabled my account. Ugh.. sickening, as I make a very decent AdSense income. I managed to email for an hour and they reinstated my account but said I must manage my database queries or the account will be gone. I'll likely start looking into switching hosts.. which blows because I have almost 50 sites. Anyway... The problem that is always caused is by simply deleting records over 30 days. This sometimes takes a minute or two.. and sometimes, the process gets stuck (as verified when looking at the SPIDs in SSH). Since I'm no MySQL expert, I wanted to ask if anyone knew of a better way to run my deletes? All my websites use a 5 column, single table database. Here's the query I currently run (from my php page): <? include 'config.php'; $result = mysql_query("DELETE FROM things WHERE date < DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 MONTH) ORDER BY date") or die(mysql_error()); ?> I had a thought that maybe I could rewrite this to delete 500 records at a time (for example) and keep looping until the > 30 days had been met. Is there an even better way to handle this problem that someone might suggest? I'm being told my querys can't run for more than 20 seconds at a time (so this is a processor issue - not memory). Thank you.

Ahh! Makes perfect sense. Don't know why I didn't think of it! Thanks much for your help!

So "approved" is some sort of "built-in" column whereby just creating it, mysql will know to hide the entire row until set to 1?

Hello. I'm not a php programmer. I just play one on the internet. Point being that I know enough to make small things happen and can slop some code together for little ideas. For my newest idea, I'm trying to have a user submitted form that requires admin approval. I know how to create a form and do the mysql insert, but how I do create a "hold" on the data being sent from a form, so that I can require approval? I'm looking for the easiest way.. If I need to approve directly through mysql (instead of creating an admin.php page), then that would be fine. I don't anticipate a lot of submissions. Just wasn't sure how this is usually handled. Thanks!

Yeah.. I have the values. I have 50 hyperlinked states with the number of mysql entries beside them, such as: florida 85 texas 97 ....just thought it would be cool to make the number (not a hyperlink) have a little more pizazz... and dress it up by putting a box around it. I've been trying other ways now. Rather I use span or div though... it keeps line breaking on me. I even used span and assigned white-space: nowrap; to it, but it still line breaks.

Ah... I didn't realize they were using a widget. Bummer. That doesn't help as I'm just trying to surround my db # with the same look.. nothing to do with sharing. Thanks though.

I am wanting to reproduce the look of the little callouts for my numbers (which are counts for topics in my mysql db) and get them looking like this site. Notice the little facebook, twitter and myspace icons with the numbers that (currently) show 1, 0, and 0? How can I get that little speak out look around my numbers? Thanks!

Worked beautifully to create the count. Thank you, Keith!

Alright.. I have enough trouble creating arrays but add in another layer and I really get confused. Could someone help me figure this one out? Let's say I have a mysql db with a few hundred urls that all look like so: http://atlanta.website1.com/topic1 http://atlanta.website1.com/topic2 http://atlanta.website2.com/topic1 http://florida.website1.com/topic1 http://florida.website1.com/topic2 http://florida.website1.com/topic3 http://florida.website1.com/topic4 http://florida.website2.com/topic1 Notice the state names are different and the websites. Now I want to: 1) Create the mysql query to do a total count on each state (regardless of website 1 or 2), so the result would lool like this: atlanta (3) florida (5) 2) Be able to create an array in my php code where I could at any time later in the page write a statement such as: We show that florida has <? echo myflorida_count; ?> records. If anyone could shed some light, I would be eternally grateful. Thanks!

Thanks for the script! It looks promising. I'll give it a go later in the week and see if it does trick. Much appreciated!

Wrap your mysql table output with this: $description = preg_replace("#([\t\r\n ])([a-z0-9]+?){1}://([\w\-]+\.([\w\-]+\.)*[\w]+(:[0-9]+)?(/[^ \"\n\r\t<]*)?)#i", '\1<a href="\2://\3" target="_blank" rel="nofollow">\3</a>', $description); $description = preg_replace("#([\t\r\n ])(www)\.(([\w\-]+\.)*[\w]+(:[0-9]+)?(/[^ \"\n\r\t<]*)?)#i", '\1<a href="http://\2.\3" rel="nofollow" target="_blank">\2.\3</a>', $description);

I have a couple hundred websites that I run a php/mysql script on. Every couple weeks, I manually visit every site to ensure it's up and running. Without fail, there's always one or two that have a message indicating the database has crashed. I run some pretty big cron jobs on them every night.. so it just happens. Can someone recommend a way that I could poll all of my mysql databases and somehow email/alert me when one goes down? Thoughts? Thank you.

Thanks much! I'll give it a whirl.

I continue to learn php... and still without the ability to create joins in mysql. Not a great mix... but, I need some quick help. Can someone join this for me? I need the select statement for my php code. select id, title, categoryid from images select id, category from categories images.categoryid = categories.id I'm trying to construct a url. Thanks.

PERFECT!! Thanks so much. Made my day.. sorry to be so programatically challenged. Hope you have a great day.