-1

I'm creating a function to help me return 1-M (One to Many relationship) results from my database.

What I want to achieve:

I have a form with a Location dropdown.

I want a function that will take the Column name (in this case "Location") and The Table name of the Table that I want to STORE the results in. Then reverse lookup the Column's.

<select>
    <option value="1">London</option>
    <option value="2">Somewhere else</option>
    <?
        $array=DataLink("location","form1");
        php foreach($array as $row){
            echo "<option value=\"".$row[0]."\">".$row[1]."</option>";
        }
    ?>
</select>

My Database would look like this:

|Form1                    |          |Locations                    |
---------------------------          -------------------------------
|Name                     |          |ID                           |
|Location                 |          |CityName                     |
|OtherInfo                |          -------------------------------
---------------------------

Now I can see that the easy solution would be to simply use the Column name to relate to the specific Table that holds the info for that column. But I don't want to do that. I'm wanting to use a "Foreign Key" of sorts, or some other method to find the relationship between a column and it's 1-M link...

Can anyone tell me how they would approach this. Is there a way to link tables together like this so that we can see the relationship between the data etc?

Thanks in advance to any and all who contribute to this.

---EDIT--- As a side note, since this seemed to raise issue with programming standards. I'm working on a system where several people are developing several different modules etc. My tables are all prefixed with my uniqueID (24) so my table Form1 is actually 24_Form1 and Location is actually 24_Locations. If someone were to want to use my Locations list for themselves, they would simply link their xx_FormY to my 24_locations, and be able to use the same function to pull the appropriate info. It's better than me naming every column in my table 24_xxxx, and forcing them to do the same. Thanks

TolMera
  • 452
  • 10
  • 25
  • A good program to use for this is MySQL Workbench. This has a nice GUI to create lonks inbetween tables. Then you can create an SQL CREATE statement in mysqlWorkbench and look at the code. There you will see how to create a Foreign key. Note however that not all DMS allow foreign keys. For instance the MySQL myisam doesn't support Foreign keys. And if you effectivly link tables together, you will not be able to TRUNCATE them. – Pinoniq Jul 18 '13 at 12:59
  • Ok yes, I have worked with MySQL Workbench. I know the foreign Key relationships, but I was specifically wondering what way's the info could be used by PHP to allow a reverse lookup to the linked table. What would you do. I just found the "SHOW CREATE TABLE Form1" allows me to see the links. Credit to http://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column – TolMera Jul 18 '13 at 13:06
  • Php can't access a Database directly. It can however query it. So you will have to write an SQL query that does what you want it to do, and then query it using mysqli_* or PDO. – Pinoniq Jul 18 '13 at 13:08
  • Yea, PHP need's to be connected to the Database, then use something like mysql_query("SHOW CREATE TABLE Form1");, in what way would you go about finding the reverse lookup? Core of the question – TolMera Jul 18 '13 at 13:10
  • first of, DONT use mysql_* functions. They are outdated, insecure an crap. use mysqli*_ or PDO. second, have you tried anything at all? And what exactly are you trying to do? bcause the entire reverse lookup thing sounds like you have no idea what you are doing – Pinoniq Jul 18 '13 at 13:17
  • Yes I've tried, I have used the "SHOW CREATE TABLE" Passed it to REGEX and pulled the foreign keys, pulled the table names from the foreign key's and then pulled all rows from the foreign table. Giving me the results that I am looking for. But I wanted input from others on how they would do it, because I might learn something, and my method seems clunky.. Perhaps there's a simple 1 liner SQL that will do it for me?? As for my using mysql_, I have not moved to the newer mysqli, but you are welcome to educate me. – TolMera Jul 18 '13 at 13:24

1 Answers1

0
function RDataLink($field, $table, $current=""){
accessDB();
    $QTable=mysql_query("SHOW CREATE TABLE $table");
    $Table=mysql_result($QTable,0,1);
exitDB();
// CONSTRAINT `jobSeeker_ibfk_1` FOREIGN KEY (`location`) REFERENCES `24_location` (`UID`)
$x=preg_match_all('/FOREIGN[^(]+.[^(]+[^)]+./',$Table,$matches);
foreach ($matches as $array){
    foreach ($array as $value){
        if(preg_match("/(?<=FOREIGN\sKEY.{3})$field/",$value)){
            if (preg_match("/(?<=REFERENCES[^`].)[^`]+/",$value,$ForeignTable)){
                accessDB();
                    $Qrows=mysql_query("SELECT * FROM ".$ForeignTable[0]);
                exitDB();
                $options="<select name=\"$field\">";
                while ($rows=mysql_fetch_array($Qrows,MYSQL_ASSOC)){
                    $options.="<option value=\"".$rows['UID']."\"".($current==$rows['UID'] ? "selected" : "") .">".$rows['name']."</option>";
                }
                $options.="</select>";
            }
        }
    }
}
if(!$x){
    return false;
}
    else {
    return $options;
}
}

function RQuery($field,$table,$current){
accessDB();
    $QTable=mysql_query("SHOW CREATE TABLE $table");
    $Table=mysql_result($QTable,0,1);
exitDB();
$x=preg_match_all('/FOREIGN[^(]+.[^(]+[^)]+./',$Table,$matches);
foreach ($matches as $array){
    foreach ($array as $value){
        if(preg_match("/(?<=FOREIGN\sKEY.{3})$field/",$value)){
            if (preg_match("/(?<=REFERENCES[^`].)[^`]+/",$value,$ForeignTable)){
                accessDB();
                    $Qrow=mysql_query("SELECT name FROM ".$ForeignTable[0]." WHERE UID='$current'");
                    return mysql_result($Qrow,0);
                exitDB();
            }
        }
    }
}

}
TolMera
  • 452
  • 10
  • 25