Help with PHP and MySQL

Navigation
1

I have 2 tables; 

[links]
id
link
clicks

Exemplo de Link cadastrado
ID     link                       clicks
1      http://www.google.com.br   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, he / she arrows that user id by clicking it, adding the id inside the "clicks" field, 

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

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

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

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

ID     link                       clicks
1      http://www.google.com.br   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 link showing their name and email.

I honestly can not think of code structure and logic.

    
asked by anonymous 17.05.2017 / 20:29

4 answers

0

You need to relate the clicks to another table, it can be just the ids, of the users x links, this way it will be easy to work with the data.

However, if you notice the field clicks, the inserted data is / are an array. you can manipulate this array to get the data separated. It's pretty easy to work with arrays, check it out here

link

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

    
17.05.2017 / 20:47
0
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 did not develop the whole logic. But it solves your problem.

    
17.05.2017 / 20:47
0

Try 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

    
17.05.2017 / 22:26
0

The correct one would be to create a new table to address this issue. But the way you're doing, I've seen you do a lot of similar reporting for using json_encode and json_decode.

    
17.05.2017 / 22:39
Installation Cordova How to make the webview work, I tried it but it does not work