3dhomejoe
Members-
Posts
18 -
Joined
-
Last visited
Never
Profile Information
-
Gender
Not Telling
3dhomejoe's Achievements
Newbie (1/5)
0
Reputation
-
[PHP + MYSQL] Selecting rows where column is blank
3dhomejoe replied to 3dhomejoe's topic in PHP Coding Help
Awesome, thanks that worked out Thanks Joe -
Hello, Im trying to select a row in my database that has no value for a column and then the script generates a sha256 hash for it and updates that row. Sounds easy, something I would rather do by hand but with 200k+ rows its not worth my time. I have tried different things eg. put null in for (WHERE SHA256 ='') but that did not do anything, $num_rows just returns 0 all the time. I included a screenshot of my database so you an see whats going on. Thanks Joe <?php //die("This script does not work right now."); include ('db.php'); $con = mysql_connect($host,$username,$password); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db($database, $con); $result = mysql_query("SELECT * FROM Main WHERE SHA256 = ''"); if(!$result) { $err=mysql_error(); print $err; exit(); } $num_rows = mysql_num_rows( $result ) ; if($num_rows ==0){ print "Nothing to do."; } else { echo "About to process $num_rows rows <br />\n" ; while($row = mysql_fetch_array($result)) { $id = $row['SHA256']; // echo "Deleting encid: $id <br />\n"; echo ("Updating $id"); $sha256 = hash('SHA256', $id); mysql_query("UPDATE Main SET SHA256 = '$sha256' WHERE Text = '$id' "); } } mysql_close($con); ?> [attachment deleted by admin]
-
Ok, thanks everyone for your help, I got some code that works... <?php $con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORDHERE'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("eq2", $con); $result = mysql_query("SELECT encid FROM encounter_table where starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); if(!$result) { $err=mysql_error(); print $err; exit(); } $num_rows = mysql_num_rows( $result ) ; if($num_rows ==0){ print "Nothing to do."; } else { echo "About to process $num_rows rows <br />\n" ; while($row = mysql_fetch_array($result)) { $id = $row[0]; echo "Deleting encid: $id <br />\n"; mysql_query("DELETE FROM attacktype_table WHERE encid='$id' "); } } mysql_close($con); ?>
-
(Too bad I can't edit my own post...) Also, is there anyway I can have mysql not send information back to the webserver so it does not have to wait for a reply? Thanks Joe
-
Could it be that my database is so large that is making this happen? I would not think that it would matter.
-
that is what it gave me... My DB's Name is eq2 My tables are attacktype_table combatant_table current_table damagetype_table encounter_table swing_table
-
And for the other line you gave me, here is what it said... for $query = "DELETE a FROM attacktype a INNER JOIN encounter_table e WHERE a.encid = e.encid and e.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY);"; I also have uploaded an image from the first line of code I ran, IIS gave it to me when I killed the Mysql command, not sure if it will help or not. [attachment deleted by admin]
-
This just loops it looks like... $query = "DELETE a FROM attacktype_table a, encounter_table e WHERE a.encid = e.encid and e.starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY);"; Mysql reported that the command was in the state of "Sending data", I left it running for about 551 sec before I killed the command, going to try the other one now
-
It gives me this... It says the same thing as before, same thing for each line you provided.
-
They both give me errors, the code that I had before worked, but it was just stuck in a loop $query = "DELETE FROM attacktype_table WHERE encid IN (SELECT encid FROM encounter_table WHERE starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY))"; mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query");
-
Does anyone have new information on this?
-
Version is "MySQL 5.0.77" for mysql version
-
here is what i get with that code...
-
Ok, here is an update, I got a code that kinda works, it selects the data and removes it but its like stuck in a loop even though I don't have a loop in my code... <?php $con = mysql_connect('10.0.1.65','3dhomejoe','PASSGOESHERE'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("eq2", $con); $query = "DELETE FROM attacktype_table WHERE encid IN (SELECT encid FROM encounter_table WHERE starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY))"; mysql_query($query) or die('MySQL error: ' . mysql_error() . "<br>\nQuery: $query"); mysql_close($con); ?> here is what it was doing... The code was running for about 2 hours b4 I killed the command Any help? Thanks Joe
-
Hello, im trying to build a script that gets the date of an old row and select an ID number from it and then it would remove any rows the = that ID number, here is my code so far it selects all of the id numbers but I can't get it to clear them, what is wrong? (Using mysql) Thanks 3dhomejoe <?php $con = mysql_connect('10.0.1.65','3dhomejoe','PASSWORDHERE'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("eq2", $con); $result = mysql_query("SELECT encid FROM encounter_table where starttime < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"); if(!$result) { $err=mysql_error(); print $err; exit(); } if(mysql_affected_rows()==0){ print "Nothing to do."; } else { while($row = mysql_fetch_array($result)) { //echo "Clearing"; echo $row[0]; mysql_query("DELETE FROM attacktype_table WHERE encid=$encid"); } } mysql_close($con); ?> something else that I tried is this direct in my sql database and it did nothing even though the encid number existed a few times also I tried this gave nothing but a white page