Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Multiple values stored in single field with relationship to table.

i have inherited a mysql database and the developers have used muliple values in a field seperated with an @ symbol.
this field contains reference numbers which relate to another table in the database.

ok so table 1 has this

propid       Charac
1             @41@869@48@874@877@878@879@881@885@870@898@901@903@
2             @41@869@48@874@877@878@879@881@885@870@898@901@903@

Table 2 has this
keyid           descr
41              desc1
42              desc2
869            desc3
48              desc4
etc


i want to run a query so i get this

propid           Descr
1                   desc1
1                   desc3
1                   desc4
2                   desc1
2                   desc3

is this possible??
Thanks all!!


0
Craigster2006
Asked:
Craigster2006
1 Solution
 
RoonaanCommented:
It would have been for more easier when he/she would have used comma's rather than @'s. Is it possible for you to change this easily, or is that not going to work for the rest of the application?

-r-
0
 
Craigster2006Author Commented:
Hi,
unfortunately its not possible to change the @ symbol - this is a db for another app.
0
 
m1tk4Commented:

select * from table1 join table2 on table1.charac like concat('%@',table2.keyid,'@%)

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Muhammad WasifCommented:
try this one

select table1.propid, table2.descr from table2
INNER JOIN table1 ON
table2.keyid IN (REPLACE( SUBSTRING(table1.Charac,2), '@', ','))
0
 
limneosCommented:
$query1="SELECT * from table1 where 1";
$result1=mysql_query($query1);
while ($row=mysql_fetch_array($result1)){
$a=explode("@",$row[Charac]);
foreach ($a as $value){
if ($a!=""){
$query2="SELECT * from table2 where keyid='$value'";
$result2=mysql_query($query2) or die(mysql_error());
$row2=mysql_fetch_array($result2);
print "$row[propid] $row2[descr]<br>";
}
}
}
0
 
Craigster2006Author Commented:
Thanks Perfect man!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now