bulgin

Okay, thank you for your help.

Thank you! That works great! Now one last quick question and I can sleep: what if I have more than one table that needs to have rows deleted in the same scenario. Following up on your post, say, for example, to delete rows that exist in t1 that have no match in t2 OR t3 that looks like a tricky one. I can of course just use the same script and change the table.column names, but I think there must be a smoother way to do it.

I want to find all those records in table 'b' that have NO corresponding match on userid in table 'a', and then delete those non-matching records from table 'b'. Userid is existing in both table 'a' and 'b' I know this is basic but my mind is drawing a blank right now and I could use some help. Thanks.

Are you referring to the $mailer array or the ones below in the "while" loop? Thank you.

Thanks I made that change!

We will soon be implementing an internal auditing mechanism which uses email alerts for certain events. I've settled on the following code and would appreciate any feedback from the group on any obvious MySQL errors or flaws that my eyes may have missed. I am particularly keen on knowing if the last MySQL update statement would in fact be alright for a system that may have thousands of entries in the tables. Thanks in advance. <?php include 'config.php'; include 'opendb.php'; $mailer = mysql_query("SELECT substring( web1_access_log.request_uri, 9 ) , web1_access_log.sent_or_not_sent, web1_access_log.request_time, timestampadd(hour,4,from_unixtime(web1_access_log.time_stamp)) AS real_time, web1_access_log.remote_host, web1_access_log.id, access1.access1_subject, timestampadd(hour,4,from_unixtime(access1.real_epoch_time)) AS time_date , access1.access1_widget, access1.access1_monitored_email, access1.access1_alert_email_address FROM web1_access_log JOIN access1 ON substring( web1_access_log.request_uri, 9 ) = access1.access1_widget where web1_access_log.sent_or_not_sent = '0'") or die (mysql_error()); while($user = @mysql_fetch_array($mailer)){ $original_time=$user[time_date]; $id=$user[id]; $time=$user[real_time]; $ip=$user[remote_host]; $to=$user[access1_alert_email_address]; $subject="Alert"; $original_subject=$user[access1_subject]; $monitored_account=$user[access1_monitored_email]; $body="On " .$time. " UTC, there was a violation from IP address " . $ip . ".\n" . "Subject line of Mail: " ."\"" . $original_subject ."\""."\n". "This message was originally created on ".$original_time." UTC." . "\n" ."Account: " .$monitored_account. "\n" . "Our internal reference ID:" .$id; mail($to,$subject,$body) ; mysql_query("UPDATE web1_access_log, access1 set web1_access_log.sent_or_not_sent = 1 where substring( web1_access_log.request_uri, 9 ) = access1.access1_widget") or die (mysql_error()); } ?>

Hi. They all worked but mtoynbee's gave me exactly what I wanted. Thanks all!

table users: Field Type Null Default Comments userid int(11) No groupid int(11) No entered datetime Yes NULL modified timestamp No CURRENT_TIMESTAMP

I tried an OR and it shows ALL users, not just those who are in BOTH groupid

I know this is basic stuff but I'm stumped and starting to learn. I have one table, users and it contains a groupid. In all cases users are listed in groupid = 2 or groupid = 4. Sometimes they are listed in both groupid 2 AND groupid 4. Why doesn't this work? SELECT * FROM `users` WHERE groupid = 2 and groupid = 4; I just want to find those users who are in both groups, 2 and 4. Thanks

Thank you! That looks perfect!

Thanks! That looks like a good place to start. I will try it and see what happens. The age function that you speak of... yes... I would like it if you could elaborate. Thanks.

Using Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2 I have an application that I'm building that examines apache logs (which are logged to a MySQL database). There are certain strings that the MySQL database is on the alert for (specially crafted URLs). If MySQL sees one of these specially crafted URLs, it is supposed to send out an email alert to an administrator. That part I have working fine. This is the problem, though. A malicious user could determine what those specially-crafted URLs are supposed to look like (difficult but not impossible given the nature of how this system works) and then send a spam-load of them against my apache server thereby setting off a flurry of outbound emails. Normally, these URLs appear very rarely and the alerts are generally limited to under 100 per day. But I run a cron job that examines the logs every 2 minutes to see if a URL has appeared, and if so, send out an alert. I believe what I need to do is have a MySQL query that sees the first occurrence of the URL, then sees if there is another one or several more just like it within a specified time frame, if not, send the alert, if so, only send the first alert and ignore the others. I'm a little lost on now to do this and would appreciate some pointers. Maybe something with counting? Thanks.

Thanks for your reply, PFMaBiSmAd. I'm not much of a php or mysql guru but understand basic principles. So if I wrap values in variables then that information won't be visibly sent to the browser? Put another way, what is the general method of passing data to session variables from my application? And if I do that, you are saying this information -- in my case MySQL field names are visible in the 'tamper data' application -- will no longer show up in the 'tamper data' application? Thank you.

I have just spent much time constructing an application only to find out that if I use Tamper Data I can forge input values to the database for fields that were supposed to be 'read only'. What is the general methodology for preventing a user from inputting values through a tool like 'Tamper Data'? If I ssl enable the site would that help? Thanks.