Inserting multiple values into the same mysql column

ebotelho · April 3, 2012

Hello everyone.

(if you want a quick description of the problem skip this part)

I'm currently developing a website that manages our soccer games. It has a userlist and a gamelist stored in a mysql table.

The userlist table is called 'usuários' and the game table is called 'jogos'.

This table jogos has the following fields: id, data (day), conf (confirmed players), nconf (not confirmed), criado (day the game was created).

What I wanna do is creating a page where is possible to create a game in a certain day, then list all the users registered so the admin can pick who's gonna play.

After that, everyone that is confirmed should be put on the conf part of the table, while those who didn't confirm should be shown on the nconf part of the table.

(and look here)

However, this is giving me some trouble right now. I do not know how can I list every player not yet confirmed on a column of a table.

My code is given below

<?php 
require_once("configs.php");
session_start(); 
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Criar Pelada</title>
<script language="javascript" src="calendar.js"></script>

</head>

<body>
<div id="registro">
        <form action="" method="post" name="form1">
            <input type="hidden" name="criarpelada" />
		<select name="dia">
		<?php
		//seletor de dias
			$i;
			for ($i = 1; $i<32; $i++) {
            		echo ("<option value=".$i.">".$i."</option>");
			}
		?>
            </select>

            <select name="mes">
		<?php
		//seletor de mês
			$m;
			for ($m = 1; $m<13; $m++) {
            		echo ("<option value=".$m.">".$m."</option>");
			}
		?>
		</select>

		<select name="ano">
		<?php
			//seletor de ano
			$a;
			for ($a = 12; $a<15; $a++) {
            		echo ("<option value=".$a.">".$a."</option>");
			}
		?>
		</select>

            <?php
		$buscar = "SELECT * FROM usuarios";
		$listar = $con->query($buscar);
		if ($listar->num_rows > 0) {
			echo ("<p>Quem está convidado?</p><br>");
			while ($encontrado = $listar->fetch_object()) {
				echo("<input type=\"checkbox\" name=\"confirm\">  ".$encontrado->first." ".$encontrado->last."<br>"); //listar usuário
			}
		}
		//adicionar usuários à lista de nao confirmados
		if ($_POST['confirm']) {

		}

			$dd = $_POST['dia'];
			$mm = $_POST['mes'];
			$aa = $_POST['ano'];

			$data = strtotime($dd."-".$mm."-".$aa);
			//test
			//echo ($data);

			//$criar = $con->query("INSERT INTO jogos (data,conf,nconf,criado) VALUES ('$data','$conf','$nconf',now())");
			//echo("<p>Pelada criada no dia ".$dd."/".$mm."".$aa."</p><br>
			//		<p>Você está automaticamente confirmado</p>");
		?>

            <input type="submit" name="enviar"/>
        </form>
   	</div>
</body>
</html>

ps: I know there are a lot of php calls, and I did so because the processing happens in the same page.

smerny · April 3, 2012

i didnt look at the code because i don't think the basic idea is right..

you should not put a bunch of data into a single column... go for better normalization... create other tables like..

jogos: id, data, criado

players: id, name, etc

conf_players: jogos_id, player_id

nconf_players: jogos_id, player_id

adding:

so to get conf_players for a certain jogos... something like this:

$query = "SELECT p.name FROM players AS p, conf_players AS cp

WHERE cp.jogos_id='$jogos_id' AND cp.player_id=p.id"

Psycho · April 3, 2012

I agree with smerny, except that I would suggest using a proper JOIN in the query. The above query has a problem in that it would not return a result if there were no confirmed players. So, if you create the game first, then go to another page to show the game and the confirmed players it wouldn't even show the game details. Also, if you need the confirmed AND unconfirmed players you need to do a second JOIN using the RIGHT parameter.

The following example would return the details of the match and the confirmed players

SELECT j.data, j.criado, u.player_name
FROM jogos AS j
LEFT JOIN confirmed AS c ON j.id = c.jogos_id
RIGHT JOIN usuários AS u ON u.player_id = c.player_id
WHERE j.id = '$game_id'

This is just off the top of my head and not tested, but it should be pointing you in the right direction.

mikosiko · April 3, 2012

or drop completely the nconf_players table and just add a boolean status column (1=confimated, 0=non-conf) to the bridge table (conf_players... even when I will rename it as "jogos_players" most likely to represent the relationship more clearly.).

the queries will more easy in that way... jmho

Psycho · April 3, 2012

or drop completely the nconf_players table and just add a boolean status column (1=confimated, 0=non-conf) to the bridge table (conf_players... even when I will rename it as "jogos_players" most likely to represent the relationship more clearly.).

the queries will more easy in that way... jmho

The drawback with that is that every time you create a new "game" you have to populate the intermediary (i.e. bridge) table with records for all the users. Then if you add players you have to also add records to the intermediary table for all the existing games. That requires a lot more overhead and is not a normalized database. Any new players that are added shouldn't require you to add records to associate that player with all the existing games with a default of unconfirmed. The lack of a confirmation records implies that they are unconfirmed.

mikosiko · April 3, 2012

The drawback with that is that every time you create a new "game" you have to populate the intermediary (i.e. bridge) table with records for all the users

not really... if you read the OP objectives again... specially this sentence

What I wanna do is creating a page where is possible to create a game in a certain day, then list all the users registered so the admin can pick who's gonna play.
After that, everyone that is confirmed should be put on the conf part of the table

therefore... the user is picking all the player that will possibly play... confirmation is post this process, and will involve only those users assigned to that "jogo" NO all the users in the table users as you imply... those users that are finally NOT confirmed can be simply removed of the bridge table... simple.. not need to deal with more tables. but again.. is just mho... your millage can be different.

ebotelho · April 9, 2012

Thank you so much for your help. Indeed it was much more organized and clean. You guys make a nice team, because your information complemented each other perfectly!

I worked with the two new tables, conf_players and nconf_players.

Fields: id_conf_players, id_jogos, and id_players and used exactly the same query Psycho mentioned above.

EDIT: as to the not confirmed players who confirm, these players need to be inserted in the confirmed players table. Is there a way to replace the information between two different tables? Or is it mandatory to perform an insert and delete? In this case, is it possible to do so in the same query?

Sign In

Inserting multiple values into the same mysql column

Recommended Posts

ebotelho

Link to comment

Share on other sites

smerny

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

ebotelho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information