how do I loop inside a loop with PHP in MySQL?

Question

table persons

name  |   details
------------------
mathew| tax,home,car,insurance
john  | job,tax,employ
neil  | tax,home,car,job
yancy | consultant,rent,family
lucy  | home,car,insurance

I want loop through this table and search with details then saved result to another table called persons1

name  |   names
------------------
mathew| neil,lucy,john
neil  | mathew,lucy,john
john  | mathew,lucy,neil

so far I coded something like below but not working

mysql_connect("localhost", "root", "pass"); 
mysql_select_db("database");
$query = "SELECT * FROM persons"; 
$result  = mysql_query($query); 
while($r = mysql_fetch_array($result)) 
{ 
    $exp = explode(",",$r["details"]);
    $sql = mysql_query('SELECT * FROM persons WHERE MATCH (tags) AGAINST ("+$exp[0]" "+$exp[1]" "+$exp[2]" IN BOOLEAN MODE)'); 
    $result = array(); 
    while($row = mysql_fetch_assoc($sql))
    {   
        array_push($result,$row['name']);
        $name = implode(",",$result);   
        mysql_query("INSERT INTO person_new (name,names) VALUES (\"".$r["name"]."\", \"".$name."\")");
    } 
}

IT is very sad that nobody can give an answer to my question about my code. instead of looking into my design I request you to look into my code and tell me where I made a mistake..i am doing something different than what it sees and this is why I request you to check my code...

normalise your table design, its a terrible idea to have more than one piece of discreet data per cell. — user557846
– user557846, Commented Feb 2, 2011 at 2:07

Dolph · Accepted Answer · 2012-02-25 20:30:09Z

4

Your problem would be better solved via database normalization.

Storing data like tax,home,car,insurance in a single column, then parsing it to search is a Very Bad Idea.

edited Feb 25, 2012 at 20:30

answered Feb 2, 2011 at 2:08

Dolph

50.9k13 gold badges66 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

aron n Over a year ago

I agree but I need it this way

Alex Budovski Over a year ago

@aron, No you don't need it this way. You have a design problem. The longer you ignore it, the more you'll paint yourself into a corner.

aron n Over a year ago

sorry I did not mean that...but this is for a new application which will redesign later but just now the format should be like this and that's why I am requesting..

aron n Over a year ago

I have enabled FULL TEXT SEARCH for this table

user7675 Over a year ago

There's never time to do it right, but there's always time to do it over.

deceze · Accepted Answer · 2011-02-02 03:45:41Z

First of all, it'd be nice if you'd tell us what doesn't work.

Having said that, I suspect (at least one of) your error(s) is here:

'SELECT * FROM persons WHERE MATCH (tags) AGAINST ("+$exp[0]" "+$exp[1]" "+$exp[2]" IN BOOLEAN MODE)'
                                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This will literally give you the query AGAINST ("+$exp[0]" "+$exp[1]" "+$exp[2]" IN BOOLEAN MODE), which is likely not what you want. You need to concatenate the string, or use a double quoted string:

'SELECT ... AGAINST ("+' . $exp[0] . '" "+' . $exp[1] . '" "+' . $exp[2] . '" IN BOOLEAN MODE)'

or

"SELECT ... AGAINST (\"+$exp[0]\" \"+$exp[1]\" \"+$exp[2]\" IN BOOLEAN MODE)"

I'm with @Dolph though, this is not a good database structure, and if you're going to redesign it later anyway (careful with saying "later", that usually never happens), you should just do it now.

Collectives™ on Stack Overflow

how do I loop inside a loop with PHP in MySQL?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related