Help with PHP and Mysql

Question

I have 2 tables;

[links]
id
link
clicks

Exemplo de Link cadastrado
ID     link                       clicks
1      http://www.google.com   NULL    
2      http://www.gmail.com       NULL   

[users]
id
nome
email

Exemplo de user cadastrado
ID      NOME           EMAIL
400     XXXXXXXXX      [email protected]
100     AAAAAAAAAA     [email protected]
250     DDDDDDDDD      [email protected]

When the user clicks on a link, it arrow that user id clicked it, adding the id inside the "clicks" field, getting:

ID     link                       clicks
1      http://www.google.com   400

If another click also on the same link, it looks like this:

ID     link                       clicks
1      http://www.google.com   400, 250

If another click again on the same link, it looks like this:

ID     link                       clicks
1      http://www.google.com   400, 250, 100

And so with the other links etc.

What I need is via PHP and Mysql to know for example, everyone who clicked on the gooogle.com.br link showing his name and email address.

Honestly I’m not getting to think about the structure of the code and logic.

Answer 1

You need to list the clicks in another table, it can only be the ids, users x links, this way it will be simple to work with the data.

However, if you look at the field clicks, the entered data is/is an array. Voce can manipulate this array to obtain the separate data. It is very easy to work with arrays, take a look here

https://secure.php.net/manual/en/language.types.array.php

make a looping foreach for each array, so you can get the individual values.

Answer 2

select clicks from links where link = "http://www.gmail.com";

$clicks= explode(",",$row->clicks);
for($x=0;$x<count($clicks);$x++){
$sql = "select nome, email from users where id = ".$clicks[$x];
}

I haven’t developed the whole logic.
But that solves your problem.

Answer 3

Try to make the following query:

select * from users where find_in_set
(id, 
    (
        select clicks from links where id = 1 /*id do link que deseja consultar*/
    )
);

SQL FIDDLE

Answer 4

The correct thing would be to create a new table to address this issue. But the way you’re doing, I’ve seen them do it very similar for reporting using json_encode and json_decode.