$_GET[ID] from two tables

Question

I am currently trying to fetch two segments of data from two different tables and display them in an "edit" type format.

I can get either "incident" or "location" to displaying using the $_GET[id] variable as a way to use the incidentID primary key reference. I thought about setting my Databases auto sequence so that incident and location would be sync, but that is not really a solution.

How would one go about using the $_GET[id] variable to call two different tables?

The code is as follows, on a side note I am aware that my sql is open for injection and I should be slapped on the wrist for not using PDOs.

I can add the DB structure if that would at all help but I would prefer to leave the DB structure where it is at currently.

searching for the data in the first place FILE) . '\connection.php';

// Process the search query
if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){
// run code if condition meets here 
    $searchquery = preg_replace('#[^a-z 0-9?]#i', '', $_POST['searchquery']);
    if($_POST['filter1'] == "0"){
        $sqlCommand = "SELECT * FROM `incident` WHERE `iTypeID` = 0 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "1"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "2"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filer1'] == "3"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 3 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "4"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "5"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "6"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }else if($_POST['filter1'] == "7"){
        $sqlCommand = "SELECT incidentID FROM `incident` WHERE `iTypeID` = 2 AND `disasterName` LIKE '%$searchquery%'";
    }
    $query = mysql_query($sqlCommand) or die(mysql_error());
    $count = mysql_num_rows($query);


    if($count >= 1){
        while($row = mysql_fetch_array($query)){
            $incidentID = $row["incidentID"];
            $dangerLevel =$row["dangerLevel"];
            $search_output .= "Item ID: <br> $incidentID <br> Danger Level: <br> $dangerLevel<br/> 
            <a href=\"modify_incident.php?id=" . $row['incidentID'] . "\">Modify Entry</a> 
            <span> </span>
            <a href=\"delete_incident.php?id=" . $row['incidentID'] . "\">Delete Entry</a> <br /><br />";           

        } // close while
    } else {


    }
}
?>

<html>
<head>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<fieldset>
<legend>Incident Search Form</legend>
<p><label>Search a Disaster (by name): <input name="searchquery" type="text"></label></p>
<p><label>Search by Type<select name="filter1"></label></p>
<option value="0">None</option>
<option value="1">Fire</option>
<option value="2">Flood</option>
<option value="3">Hurricane</option>
<option value="4">Tropical Storm</option>
<option value="5">LandSlide</option>
<option value="6">Biological Outbreak</option>
</select>
</fieldset>
<input name="myBtn" type="submit">
<br />
<br />
<div>
<?php echo $search_output; ?>
</div>
</form>     
</body> 
</head> 
</html>

Modifying the data after it has been searched FILE) . '\connection.php';

    if(!isset($_POST['submit'])){
        $q = "SELECT * FROM incident WHERE incidentID = $_GET[id]";
        $ql = "SELECT * FROM location where locationID = $_GET[id]";
        $results = mysql_query($q);
        $incident = mysql_fetch_array($results);
        $results2 = mysql_query($ql);
        $incident2 = mysql_fetch_array($results2);  
    }
?>
</html>
<head>
<body>
<h1>You are Modifying an Incident</h1>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    <fieldset>
    <legend>Incident</legend>
    <p><label>Disaster Name: <input type="text" name ="inputIncident" value = "<?php echo $incident['disasterName']; ?>" placeholder = "SampleTree Fire" /></label></p>
    <p><label>Disaster Description: <input type="text" name ="inputDescription" value = "<?php echo $incident['description']; ?>" placeholder = "Large Forest fire near"  /></label></p>
    <p><label>Time of Incident: <input type="time" name ="inputTime" value = "<?php echo $incident['time']; ?>" placeholder = "hh:mm:ss"/></label></p>
    <p><label>Date of Incident: <input type="date" name ="inputDate" value = "<?php echo $incident['date']; ?>" placeholder = "yyyy/mm/dd"/></label></p>
    <p><label>Danger of Incident: <input type="number" name ="inputdangerLevel" placeholder = "1-10" value = "<?php echo $incident['dangerLevel']; ?>" /></label></p>
    <p><label for ="type">Select Disaster Type:</label>
        <select id="type" name="type" value = "<?php echo $incident['iTypeID']; ?>"
        <option value="0">None</option>
        <option value="1">Fire</option>
        <option value="2">Flood</option>
        <option value="3">Hurricane</option>
        <option value="4">Tropical Storm</option>
        <option value="5">LandSlide</option>
        <option value="6">Biological Outbreak</option>
        </select>
    </fieldset>
    <fieldset>
    <legend>Location</legend>
    <p><label>Street Name:<input type="text" name ="inputStreet" value = "<?php echo $incident2['streetName']; ?>" placeholder = "Avalon Place" /></label></p>
    <p><label>Street Number:<input type="number" name ="inputNumber" value = "<?php echo $incident2['streetNumber']; ?>" placeholder = "9" /></label></p>
    <p><label>Suburb:<input type="text" name ="inputSuburb" value = "<?php echo $incident2['suburb']; ?>" placeholder = "Upper Kedron" /></label></p>
    <p><label>Postcode:<input type="text" name ="inputPostCode" value = "<?php echo $incident2['postCode']; ?>" placeholder = "4055" /></label></p>
    <p><label>Region:<input type="number" name ="inputRegion" value = "<?php echo $incident2['region']; ?>" placeholder = "4" /></label></p>
    <p><label>Lattitude:<input type="text" name ="inputLattitude" value = "<?php echo $incident2['mapLat']; ?>" placeholder = "136.10" /></label></p>
    <p><label>Longitude:<input type="text" name ="inputLongitude" value = "<?php echo $incident2['mapLon']; ?>" placeholder = "182.86" /></label></p>
    <p><label for ="state">State:</label>
        <select id="state" name="state" value = "<?php echo $incident2['state']; ?>">
        <option value="QLD">QLD</option>
        <option value="NSW">NSW</option>
        <option value="NT">NT</option>
        <option value="ACT">ACT</option>
        <option value="SA">SA</option>
        <option value="WA">WA</option>
        <option value="TAS">TAS</option>
        </select>
    </fieldset>
</p>



    <br/>
</fieldset>
    <input type="hidden" name="id" value="<?php echo $_GET['id'];?>"/>
    <!--<input type="hidden" name="id2" value="<?php echo $_GET['id'];?>"/>-->
    <input type="submit" name="submit" value="modify"/>
</form>
</body>
</head>
</html>
<?php
if(isset($_POST['submit'])){
    $u = "UPDATE incident SET `disasterName`='$_POST[inputIncident]', 
    `description`='$_POST[inputDescription]', 
    `time`='$_POST[inputTime]', 
    `date`='$_POST[inputDate]', 
    `dangerLevel`='$_POST[inputdangerLevel]',
    `iTypeID`='$_POST[type]' 
    WHERE incidentID = $_POST[id]";

    $ul = "UPDATE location SET `streetName`='$_POST[inputStreet]',
    `steetNumber`='$_POST[inputNumber]',
    `suburb`='$_POST[inputSuburb]',
    `postcode`='$_POST[inputPostCode]',
    `region`='$_POST[inputRegion]',
    `lattitude`='$_POST[inputLattitde]',
    `longitude`='$_POST[inputLongitude]',
    `state`='$_POST[state]'
    WHERE locationID = $_POST[id]";


    mysql_query($u) or die (mysql_error());
    mysql_query($ul) or die (mysql_error());

    echo "User has been modified!";
    header ('Location: output.php');
} else{
}
?>

Incident

Column  Type    Null    Default     Links to 
incidentID  int(15)     No                   
dangerLevel     int(2)  No                   
description     varchar(250)    No                   
time    time    No                   
date    date    No                   
isresolved  tinyint(1)  No                   
locationID  int(11)     No          location -> locationID           
isPublic    tinyint(1)  No                   
iTypeID     int(11)     No          itype -> iTypeID         
disasterName    text    No 

Location                 
Column  Type    Null    Default     
locationID  int(10)     No               
postCode    int(4)  No               
region  text    No               
state   text    No               
mapLat  float   No               
mapLon  float   No               
streetNumber    int(11) No               
streetName  text    No               
suburb  text    No

I think I might of found a different way to do it but still need a little hand on doing so, how would one expand this out so $row['incidentID'] and $row['locationID'] would be assigned to ID and ID2?

<a href=\"modify_incident.php?id=" . $row['incidentID'] . "\">Modify Entry</a>

Your script is vulnerable to SQL injections. You should definitely [fix that](http://stackoverflow.com/q/60174/53114). — Gumbo, May 26 '13 at 06:50

jspcal · Accepted Answer · 2013-05-26T07:21:56.163

0

If this is a 1:M relationship (a location can have many incidents), you'd want to join the tables together:

select * from incident i, location l where i.id = ? and i.location_id = l.id

Each row would have columns from incident and the corresponding location.

EDIT:

You can update the location via the incident ID using an update join:

update location l
join incident i on i.location_id = l.id
set l.city = ?
where i.id = ?

(You can also combine the 2 tables into one if this is a 1:1 relationship where each incident has a unique location.)

edited May 26 '13 at 07:21

answered May 26 '13 at 06:53

jspcal

50,847
7
72
76

I get that that is the correct way to do it but lets say its modify_incident.php?id=62 is there a way to have it modify_incident.php?id_1=62,id_2=29 – Brendan Ritchie May 26 '13 at 07:10

$_GET[ID] from two tables

1 Answers1