Is this possible?

[adamjones]

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!

 

[CodeMaster]

Something like this?

 

INSERT INTO badges
SELECT 
username,
"HERE_YOUR_BADGENAME_VALUE" as badgename,
NOW() as date
FROM users

 

[adamjones]

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

[CodeMaster]

Are you doing this action just one single time? If so, you might just want to execute the query directly in mysql.

[Mchl]

How did you manage to change INSERT INTO ... SELECT to SELECT ... INERT INTO... ??

[TeddyKiller]

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.

[adamjones]

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)) {"

[Mchl]

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

[adamjones]

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");
}
?>

[Mchl]

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?

[TeddyKiller]

$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?

[adamjones]

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

[CodeMaster]

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.

[adamjones]

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");

[adamjones]

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

[Mchl]

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

[TeddyKiller]

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?

[Mchl]

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?