Help needed

[TMiland]

Hi all, i am trying to get a simple script working, to update a db with names from another db, and i got some guidance from a dev, but nothing more from there.

 

This is what he says:

List reservedPlayers = results from your subs or wherever you pull the most current list, converted to an array/list of player names.

List oldReservedPlayers = results from: SELECT player_name FROM adkats_accesslist where access_level = 5;

 

loop over names in reservedPlayers, for each entry check if that name exists in oldReservedPlayers.

- If it does not exist, insert using: INSERT INTO adkats_accesslist (player_name, member_id, player_email, access_level) VALUES ("newplayernamehere", 0, "NoEmail", 5);

 

loop over names in oldReservedPlayers, for each entry check if that name exists in reservedPlayers.

- If it does not exist, delete from table using: DELETE FROM adkats_accesslist WHERE player_name = "playernametodeletehere";

 

This is what i have to get the names from the db ($reservedPlayers):

<?php

	require_once("../../class2.php");
	function reservedPlayers($uclass = 1){

    $u_sql = new db();
	$result = array();
     
    $query = "SELECT user_id, user_class, user_name FROM #user WHERE find_in_set('{$uclass}', user_class) ORDER BY user_id";

    if ($u_sql->db_Select_gen($query, true)){

        while ($row = $u_sql->db_Fetch(MYSQL_ASSOC)){
           $result[] = $row['user_name'];
        }
       
    }
	if (count($result))
	{
		return implode("\n", $result);
	}
	else
	{
		return ("Ingen i klasse ". $uclass);
	}
}
$reservedPlayers = reservedPlayers(1)."\n".reservedPlayers(;

$names = explode("\n", $reservedPlayers);
$names = array_unique($names);
$reservedPlayers = implode("\n", $names);

$file = "reserved_slots.txt";
	$handle = fopen($file, 'w') or die("can't open file");
	$data = $reservedPlayers;
	fwrite($handle, $data); 
	fclose($handle);

And this is to get players already in the target db ($oldReservedPlayers)

<?php
require_once("reserved_slots.php");
require_once("db.php");
//$oldReservedPlayers = mysql_query("SELECT player_name FROM adkats_accesslist where access_level = 5");
	function oldReservedPlayers($alevel = 5){
	
    $query = mysql_query("SELECT player_name, access_level FROM adkats_accesslist where find_in_set('{$alevel}', access_level)");
		while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
        $oldReservedPlayers[] = $row['player_name'];
        }
	if (count($oldReservedPlayers))
	{
		return implode("\n", $oldReservedPlayers);
	}
	else
	{
		return ("Ingen i klasse ". $alevel);
	}	
}
$oldReservedPlayers = oldReservedPlayers(5)."\n";

mysql_close($dbhandle);
?>

I have been trying to make this work for 2 days straight now, and have no idea what i'm doing. I am no coder, but i understand the logic, and can modify code to make it work.

 

If someone could point me in the right direction, that would have been very much appreciated! :-)

[TMiland]

A donation will come your way if you help me solve my "problem"! :-)

[Barand]

Bear in mind I don't know the table data structure but it looks like you need two queries similar to these to complete the task

INSERT INTO adkats_accesslist (player_name, member_id, player_email, access_level)
SELECT rp.player_name, 0, 'NoEmail', 5
FROM reservedPlayers rp
LEFT JOIN oldReservedPlayers orp
    ON rp.player_name = orp.player_name AND orp.access_level = 5
WHERE orp.player_name IS NULL

DELETE adkats_accesslist
FROM adkats_accesslist
JOIN (
    SELECT orp.player_name
    FROM oldreservedPlayers orp
    LEFT JOIN reservedPlayers rp
        ON rp.player_name = orp.player_name AND rp.access_level = 5
    WHERE rp.player_name IS NULL
    ) as players USING (player_name)

Edited November 6, 2013 by Barand

[TMiland]

 

Bear in mind I don't know the table data structure but it looks like you need two queries similar to these to complete the task

INSERT INTO adkats_accesslist (player_name, member_id, player_email, access_level)
SELECT player_name, 0, 'NoEmail', 5
FROM reservedPlayers rp
LEFT JOIN oldReservedPlayers orp
    ON rp.player_name = orp.player_name AND orp.access_level = 5
WHERE orp.player_name IS NULL

DELETE adkats_accesslist
FROM adkats_accesslist
JOIN (
    SELECT player_name
    FROM oldreservedPlayers orp
    LEFT JOIN reservedPlayers rp
        ON rp.player_name = orp.player_name AND orp.access_level = 5
    WHERE rp.player_name IS NULL
    ) as players USING player_name

 

Thanks for the reply! 

 

I have something to work with here:

$new_players = array_diff($reservedPlayers, $oldReservedPlayers);
$old_players = array_diff($oldReservedPlayers, $reservedPlayers);

foreach ($new_players as &$new)	{
	$query = 'INSERT INTO `adkats_accesslist` (`player_name`, `member_id`, `player_email`, `access_level`) VALUES ('.$new.', 0, "test@gmail.com", 5)';
	$result = mysql_fetch_array($query) or die(mysql_error());
}
foreach ($old_players as &$old) {
	$query = 'DELETE FROM adkats_accesslist WHERE player_name = '.$old.'';
	$result = mysql_query($query) or die(mysql_error());
	}

This is not doing anything...

 

 

Where did i go wrong? 

 

*Edited!

Edited November 6, 2013 by TMiland

[Barand]

Insert and delete queries do not return rows

$result = mysql_fetch_array($query) or die(mysql_error());

should be

mysql_query($query) or die(mysql_error())

String values need to be in single quotes

$query = "DELETE FROM adkats_accesslist WHERE player_name = '$old' ";
mysql_query($query);

Plus the above needs to be inside the loop

 

 

Also it is inefficient to run queries inside loop

Edited November 6, 2013 by Barand

[TMiland]

Insert and delete queries do not return rows

$result = mysql_fetch_array($query) or die(mysql_error());

should be

mysql_query($query) or die(mysql_error())

String values need to be in single quotes

$query = "DELETE FROM adkats_accesslist WHERE player_name = '$old' ";
mysql_query($query);

Plus the above needs to be inside the loop

 

 

Also it is inefficient to run queries inside loop

 

Thank you! Now i have this:

<?php
require_once("reserved_slots.php");
require_once("db.php");
	function oldReservedPlayers($alevel = 5){
	
    $query = mysql_query("SELECT player_name, access_level FROM adkats_accesslist where find_in_set('{$alevel}', access_level)");
		while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
        $oldReservedPlayers[] = $row['player_name'];
        }
	if (count($oldReservedPlayers))
	{
		return implode("\n", $oldReservedPlayers);
	}
	else
	{
		return ("Ingen i klasse ". $alevel);
	}	
}
$oldReservedPlayers = oldReservedPlayers(5)."\n";

echo $reservedPlayers."\n";
echo $oldReservedPlayers."\n";

$new_players = array_diff($reservedPlayers, $oldReservedPlayers);
$old_players = array_diff($oldReservedPlayers, $reservedPlayers);

foreach ($new_players as &$new)	{
	$query = "INSERT INTO `adkats_accesslist` (`player_name`, `member_id`, `player_email`, `access_level`) VALUES ('$new', 0, 'test@gmail.com', 5)";
	$result = mysql_query($query) or die(mysql_error())
}
foreach ($old_players as &$old) {
	$query = "DELETE FROM adkats_accesslist WHERE player_name = '$old'";
	$result = mysql_query($query) or die(mysql_error())
}

mysql_close($dbhandle);
?>

As the final script, but that returns this error: Parse error: syntax error, unexpected '}' in public_html/plugins/reserved_slots/reserved_slots_db.php on line 30

[Barand]

semi-colons missing from the ends of both mysql_query() lines

[TMiland]

semi-colons missing from the ends of both mysql_query() lines

Doh! Thanks! I'm blind on this code now, been staring at it all day 

 

Okay, now it's the real fun, nothing is happening... No errors, no nothing. How could i print the output of the queries in an easy way? (for debugging)

Edited November 6, 2013 by TMiland

[Barand]

As I said, no output from INSERT, UPDATE or DELETE queries.

 

Listing the arrays would show which recs were inserted and deleted

[TMiland]

As I said, no output from INSERT, UPDATE or DELETE queries.

 

Listing the arrays would show which recs were inserted and deleted

Obviously i have a lot to learn, i have no idea what i'm doing...

 

Giving up for today. Thanks for all the help so far! Much appreciated!

[TMiland]

Here we go again, from the description i have, is this right?

 

I get two player_names in one, then it stops, what am i doing wrong?

$reservedPlayers = array($reservedPlayers);
$oldReservedPlayers = array($oldReservedPlayers);

$new_players = array_diff($reservedPlayers, $oldReservedPlayers);
$old_players = array_diff($oldReservedPlayers, $reservedPlayers);

list($new_players) = $reservedPlayers;
list($old_players) = $oldReservedPlayers;



foreach ($reservedPlayers as $new)
	{
	print $new;
	}	
	if (array_key_exists($oldReservedPlayers))
	{
	return;
	}
	else
	{
	$query_new = "INSERT INTO `adkats_accesslist` (`player_name`, `access_level`) VALUES ('$new', 5)";
	$result_new = mysql_query($query_new) or die(mysql_error());
	}	

	
foreach ($oldReservedPlayers as $old) 
	{
	print $old;
	}
	if (array_key_exists($reservedPlayers))
	{
	return;
	}
	else
	{
	$query = "DELETE FROM adkats_accesslist WHERE player_name = '$old'";
	$result = mysql_query($query) or die(mysql_error());
	}	

This gives Duplicate entry 'player_name1 player_name2 ' for key 'PRIMARY'

[Barand]

You are attempting to insert PlayerX into the table when there is already a PlayerX in the table - as the error message says.

[TMiland]

You are attempting to insert PlayerX into the table when there is already a PlayerX in the table - as the error message says.

yes i know that, but how do i solve it?

 

I get 2 player names in one table, why is that?

[Barand]

 

 

if (array_key_exists($oldReservedPlayers))

 

if what key exists? array_key_exists

 

Time you turned error reporting on!

[TMiland]

if what key exists? array_key_exists

 

Time you turned error reporting on!

Thanks for the awesome help!