PHP and MySQL, having more "info" in one column

[Joveice]

So I want to be able to store etc 1, 2, 3, 4 in the same column and then in php get it out like this

$nr1 = mysql stuff // echo $nr1 will return "1"

$nr2 = mysql stuff // echo $nr1 will return "2"

$nr3 = mysql stuff // echo $nr1 will return "3"

$nr4 = mysql stuff // echo $nr1 will return "4"

how can this be done? I just need the basic way to do, the rest I'll do myself

[Mr_KoKa]

How do you connecting to database and what query you use?

[Joveice]

1 minute ago, Mr_KoKa said:

How do you connecting to database and what query you use?

mysqli_connect($db_host, $db_user, $db_pass, $db_db);

 

and

$sql = "SELECT * FROM $table WHERE team1='1,2,3,4,5' AND team2='1,2,3,4,5'";
$result = mysqli_query($conn, $sql);

 

PS: your probably gonna know my whole prodject soon

[Mr_KoKa]

So those numbers you want to split aren't in separate columns, but in one, right?

The $result holds those 'team1' and 'team2' so you need to use explode function for example on $result['team1']

 

And as always (as someone who starts with php), you should probably take a look at PDO or mysqli, as mysql extension is not longer supported.

[Joveice]

1 minute ago, Mr_KoKa said:

So those numbers you want to split aren't in separate columns, but in one, right?

The $result holds those 'team1' and 'team2' so you need to use explode function for example on $result['team1']

 

And as always (as someone who starts with php), you should probably take a look at PDO or mysqli, as mysql extension is not longer supported.

okey not sure if this is what I want or not. Gonna try explain what I need. So when I sendt the data to the mysql it's getting sendt with the team1 and team2 data and those should contain etc "1, 2, 3, 4, 5" or some other seperator. and then when I'm getting it back I need to be able to split the team1 into 5 variables for both of them.

I already know how I should get the numbers connected so thats not what I need, I just need a way to split it when I get it back I guess. (not gonna SELECT by the team tags but it was just an example)

[espurritado]

you are breaking the first normal form of a relational data base. A field for an entry may only contain one value.

A solution would be to create an additional table with entries team and value.

[Joveice]

I want it like this

 

[espurritado]

6 minutes ago, Joveice said:

I want it like this

 

as I just said, you are breaking the first normal form, the solution, again, would be a different entry for each individual state_id, city_id and spaid.

this would mean that for spaid 421 you would have 6 different entries 

[Joveice]

44 minutes ago, espurritado said:

as I just said, you are breaking the first normal form, the solution, again, would be a different entry for each individual state_id, city_id and spaid.

this would mean that for spaid 421 you would have 6 different entries 

And then how would I do that? as long as it works I'm happy. if not I'm just gonna do a workaround for now by having 10 colomns 5 for each team and 1 for each player

[vorticalbox]

15 minutes ago, Joveice said:

And then how would I do that? as long as it works I'm happy. if not I'm just gonna do a workaround for now by having 10 colomns 5 for each team and 1 for each player

like @Mr_KoKa said use explode

 

http://php.net/manual/en/function.explode.php

 

you explode the result which will put each number into an array, then you can iterate the array and do as you wish or use list to drop then into variables. Personally, I would use an array.

 

that link has an example of both.

[espurritado]

11 minutes ago, Joveice said:

And then how would I do that? as long as it works I'm happy. if not I'm just gonna do a workaround for now by having 10 colomns 5 for each team and 1 for each player

It might "work", but working with an unnormalized database implies possible problems in the future with insertions, updates and deletions of values.

You must remember, as well, that SQL is way more optimized in it's operations than any code you, or any third party may write. Iterating between the 10 rows returned by a query with a single value will be more efficient than taking a single row, sending all the values received into an array and iterating between them

[mariushm]

So you'd playing with state_id,  city_id and spa_id (i'm gonna write it like this cause it makes more sense).

And for each state_id and spa_id (probably can only show up once for a particular state_id) there's several city_id records. 

In this case, you can have either one table or two tables

 

Version 1. One table, one row per city_id

 

id , state_id, spa_id, city_id

[..]

4 , 11 , 421, 18

5, 11, 421, 19

[..]

 

Version 2. Two tables , one for  state_id and spa_id pairs,  one for  state_id and city_id pairs

 

id , state_id, spa_id   <-- let's call it table_spa

[..]

4 , 11, 420

5 , 11, 421

 

id , spa_id, city_id <--- let's call it table_cities

[...]

10, 421 , 18

11, 421 , 19

[..]

 

The first version is more difficult in case you want to quickly obtain a list of unique spa_id for a particular city, because with your table you could just do "select spa_id from table where state_id = 11" and you get your list of spa_id's, if they can only show up once per state.

But you can still work around with more complicated queries or just by using an array to store each spa_id once in the array as you retrieve rows from table.  Something like this:

 

spalist = array();

$db  = new mysqli( $host, $user , $pass , $db);

$result = $db->query ( 'SELECT * FROM table_spa WHERE state_id=11');

while ($row = $db->fetch_object($result) ) {

 $spalist[$row->spa_id] = 1;   // create an entry in the array for each unique spa_id and just set a value we don't care about

}

$result->close(); // if we work with results returned as objects, remove them from memory after we're done with them

// so now we have spalist[420] = 1 , spalist[421] = 1 etc ... so you can use a foreach ($spalist as  $number => $value) { echo $number; }  to get 420, 421 etc

// you can even sort the spalist array by index to sort your spa_id's if you don't do it in the query

The second version allows you to do a query to retrieve just the spa_id and state_id pairs , and then you could do a separate query to get the city_id's for just one state_id + city_id pair.

If you have to get the cities for several pairs of state_id and city_id, at first look it would seem you would have to do multiple queries.  But there's another way, with a bit more advanced queries.

For example, you could use the LEFT JOIN , which tells mysql to add columns from other tables to your original table based on matching some columns from first table with the second one.

In our case, we do the query on the second table, and we attach the state_id to our table

 

SELECT A.spa_id , A.city_id, B.state_id FROM table_cities AS A  LEFT JOIN table_spa AS B ON A.spa_id = B.spa_id [ optional : WHERE A.spa_id=421 ]

 

I've used aliases for the table names A and B to make it easier to read. If you have the same column name on two tables you can also use alias to make them unique

 

SELECT A.id AS id_first, A.spa_id, A.city_id, B.id AS id_second, B.state_id FROM table_cities AS A  ...