Jump to content

Is this possible?


adamjones

Recommended Posts

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

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

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

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

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

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.