adamjones Posted May 15, 2010 Share Posted May 15, 2010 Hi. I have a users table, which is basically; id|username|password|etc... and a badges table, which has; username|badgename|date I have a script where you can give a badge to a single user, but if i want to give a badge to every user, it would be very laborious. Is it possible to save all the usernames from my users table temporarly in a script and then insert them into my badges table along with a badge name?.. if you understand me? :S Thank you! Quote Link to comment Share on other sites More sharing options...
CodeMaster Posted May 15, 2010 Share Posted May 15, 2010 Something like this? INSERT INTO badges SELECT username, "HERE_YOUR_BADGENAME_VALUE" as badgename, NOW() as date FROM users Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 Something like this? INSERT INTO badges SELECT username, "HERE_YOUR_BADGENAME_VALUE" as badgename, NOW() as date FROM users Hi, thanks. This is my code, but it's not working. <?php require_once('config.php'); $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } $badge = clean($_POST['badge']); $today = date("d.m.y"); $sql="SELECT username as user FROM users INSERT INTO badges(username, badge, date) VALUES ('user', '$badge', '$today'"; $result=mysql_query($sql); if($result){ header("location: ./give_badge"); } ?> I want it to insert each user from my users table into the badges table along with the $badge Quote Link to comment Share on other sites More sharing options...
CodeMaster Posted May 15, 2010 Share Posted May 15, 2010 Are you doing this action just one single time? If so, you might just want to execute the query directly in mysql. Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 15, 2010 Share Posted May 15, 2010 How did you manage to change INSERT INTO ... SELECT to SELECT ... INERT INTO... ?? Quote Link to comment Share on other sites More sharing options...
TeddyKiller Posted May 15, 2010 Share Posted May 15, 2010 Well.. I believe what you want to do is: Get all the usernames from the users table via a query. Do a while for that query, and inside the while.. would be inserting that username into the badges table with the nessecary badge etc. Example <?php if(isset($_POST['submit'])) { $query = mysql_query("select username from users"); while($row = mysql_fetch_assoc($query)) { $today = date("d.m.y"); $query = mysql_query("INSERT INTO `badges` (username, badge, date) VALUES ('".$row['username']."', 'THE BADGE', '$today')"); } } echo 'Give badges to all users'; echo '<form action="" method="post"><input type="submit" value="submit" name="submit" /></form>'; This should work, but you can do it like what CodeMaster said. Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 Are you doing this action just one single time? If so, you might just want to execute the query directly in mysql. Well, it's a page in my CMS, which gives all users on my forum a badge, and other users have access to the CMS, and I wouldn't really want them to have access to my MySQL to do this. How did you manage to change INSERT INTO ... SELECT to SELECT ... INERT INTO... ?? Sorry? :S Well.. I believe what you want to do is: Get all the usernames from the users table via a query. Do a while for that query, and inside the while.. would be inserting that username into the badges table with the nessecary badge etc. Example <?php if(isset($_POST['submit'])) { $query = mysql_query("select username from users"); while($row = mysql_fetch_assoc($query)) { $today = date("d.m.y"); $query = mysql_query("INSERT INTO `badges` (username, badge, date) VALUES ('".$row['username']."', 'THE BADGE', '$today')"); } } echo 'Give badges to all users'; echo '<form action="" method="post"><input type="submit" value="submit" name="submit" /></form>'; This should work, but you can do it like what CodeMaster said. I receive the following error; "Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/habhubc/public_html/intra/mgive_badge_conf.php on line 90" Line 90 = "while($row = mysql_fetch_assoc($query)) {" Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 15, 2010 Share Posted May 15, 2010 How did you manage to change INSERT INTO ... SELECT to SELECT ... INERT INTO... ?? Sorry? :S I mean, how did you manage to change example given to you by CodeMaster into this? $sql="SELECT username as user FROM users INSERT INTO badges(username, badge, date) VALUES ('user', '$badge', '$today'"; Anyway, this should do it: INSERT INTO badges (username, badge, date) SELECT username, 'YourBadge', CURDATE() FROM users Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 How did you manage to change INSERT INTO ... SELECT to SELECT ... INERT INTO... ?? Sorry? :S I mean, how did you manage to change example given to you by CodeMaster into this? $sql="SELECT username as user FROM users INSERT INTO badges(username, badge, date) VALUES ('user', '$badge', '$today'"; Anyway, this should do it: INSERT INTO badges (username, badge, date) SELECT username, 'YourBadge', CURDATE() FROM users Oh, because when I ran it, It wasn't working :s And I'm just getting a blank page now. <?php //connect blah blah $today = date("d.m.y"); $badge = clean($_POST['badge']); $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', $today' FROM users"; $result=mysql_query($sql); if($result){ header("location: ./give_badge"); } ?> Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 15, 2010 Share Posted May 15, 2010 Try echoing mysql_error to see if there's any problem with this query [edit] At very least you're missing a ' before $date. Are you storing date as string? Quote Link to comment Share on other sites More sharing options...
TeddyKiller Posted May 15, 2010 Share Posted May 15, 2010 $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', $today' FROM users"; Would this actually work? it's selecting 3 information from the users table, and not really inserting it? Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 Try echoing mysql_error to see if there's any problem with this query [edit] At very least you're missing a ' before $date. Are you storing date as string? MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.10' FROM users' at line 1 Quote Link to comment Share on other sites More sharing options...
CodeMaster Posted May 15, 2010 Share Posted May 15, 2010 You forgot the quote, bro. Change $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', $today' FROM users"; Into $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', '$today' FROM users"; What this query does is, it inserts the username from the users table along with a badgename and a date into the badges table. Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 Try echoing mysql_error to see if there's any problem with this query [edit] At very least you're missing a ' before $date. Are you storing date as string? No, date = $today = date("d.m.y"); Quote Link to comment Share on other sites More sharing options...
adamjones Posted May 15, 2010 Author Share Posted May 15, 2010 Try echoing mysql_error to see if there's any problem with this query [edit] At very least you're missing a ' before $date. Are you storing date as string? You forgot the quote, bro. Change $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', $today' FROM users"; Into $sql="INSERT INTO badges (username, badge, date) SELECT username, '$badge', '$today' FROM users"; What this query does is, it inserts the username from the users table along with a badgename and a date into the badges table. THANK YOU! It's working Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 15, 2010 Share Posted May 15, 2010 No, date = $today = date("d.m.y"); As a string then. It's not a very good idea. You should use MySQL's DATE datatype Quote Link to comment Share on other sites More sharing options...
TeddyKiller Posted May 15, 2010 Share Posted May 15, 2010 No, date = $today = date("d.m.y"); As a string then. It's not a very good idea. You should use MySQL's DATE datatype May I ask, why isn't it a good idea? Quote Link to comment Share on other sites More sharing options...
Mchl Posted May 15, 2010 Share Posted May 15, 2010 Because it makes any searches more complex than selecting a single date very tedious and slow. How would you select all badges assigned in march? Or between February 14th and March 11th? Or badges assigned on fridays? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.