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! Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/ 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058848 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058853 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. Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058854 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... ?? Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058855 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. Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058858 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)) {" Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058861 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058866 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"); } ?> Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058870 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? Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058872 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? Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058874 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058877 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. Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058880 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"); Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058881 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058882 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 Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058886 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? Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058900 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? Link to comment https://forums.phpfreaks.com/topic/201888-is-this-possible/#findComment-1058906 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.