CODEWITHSUNDEEP
phpmysql
GLRotterdam
GLRotterdam

Reputation: 143

Code not showing record from other table

I created a detail page where I can filter persons by the filled in value how ever my table is not showing address and cv information. Those two are in seperate tables so I have address table and cv(a pdf file) table with their own values.

<?php

if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT person_id, person_firstname, person_lastname, 
             person_email, person_phonenumber,  
             address_street,address_housenumber, 
             address_city,address_state,address_zipcode,cv_path
      FROM person 
        inner join address on address.address_id = person.person_address 
        inner join cv on cv.cv_id = person.person_cv 
          WHERE CONCAT(`person_firstname`, `person_lastname`, `address_street`, `address_housenumber`, `address_zipcode`, `address_city`, `address_state`, `person_email`, `person_phonenumber` ) 
          LIKE '%".$valueToSearch."%'";
$search_result = filterTable($query);

}
else {
$query = "SELECT * FROM `person`";
$search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("localhost", "root", "usbw", "persons");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>PHP HTML TABLE DATA SEARCH</title>
    <style>
        table,tr,th,td
        {
            border: 1px solid black;
        }
    </style>
</head>
<body>

    <form action="testing.php" method="post">
        <input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
        <input type="submit" name="search" value="Filter"><br><br>

        <table>
             <tr>
                <th>Voornaam</th>
                <th>Achternaam</th>
                <th>Straat</th>
                <th>Huisnummer</th>
                <th>Postcode</th>
                <th>Stad</th>
                <th>Provincie</th>
                <th>Email</th>
                <th>Mobiel</th>
                <th>cv</th>
                <th>delete</th>
            </tr>;

  <!-- populate table from mysql database -->
            <?php while($row = mysqli_fetch_array($search_result)):?>
            <tr>
                <td><?php echo $row['person_firstname'];?></td>
                <td><?php echo $row['person_lastname'];?></td>
                <td><?php echo $row['address_street'];?></td>
                <td><?php echo $row['address_housenumber'];?></td>
                <td><?php echo $row['address_zipcode'];?></td>
                <td><?php echo $row['address_city'];?></td>
                <td><?php echo $row['address_state'];?></td>
                <td><?php echo $row['person_email'];?></td>
                <td><?php echo $row['person_phonenumber'];?></td>
                <td><?php echo "<a href='http://localhost:8080/website/" . $row['cv_path'] . "'>cv file</a>";?></td>
                <td><?php echo "<a href='delete.php?person_id=" . $row['person_id'] . "'>delete</a>";?></td>

            </tr>
            <?php endwhile;?>
        </table>
    </form>

</body>
</html>

The strange thing of this all is when I go to the page it shows me a error: Notice: Undefined index: address_street in D:\Server\root\Website\testing.php on line xx ( for all address and cv fiels). BUT when I filter something it will show everything like it should be so address and cv is showing.

UPDATE:

1

UPDATE2: Delete.php:

<?php
$servername = "localhost";
$username = "root";
$password = "usbw";
$dbname = "persons";

// CREATE A CONNECTION WITH THE DATABASE
// CONNECTIE MAKEN MET DATABASE
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// GET ID FROM person_id
// PAK ID VAN person_id 
$id=$_GET['person_id']; 

// CREATE PREPARE STATMENT FOR DELETING RECORDS FROM person_id
// MAAK EEN STATEMENT OM WAARDES TE VERWIJDEREN VAN person_id
$stmt = $conn->prepare('DELETE FROM person WHERE person_id = ?'); 
$stmt->bind_param('s', $id);                                         

// EXECUTE STATEMENT AND IF RESULT IS FALSE SHOW ERROR
// VOER STATEMENT UIT EN ALS VALS IS GEEF ERROR AAN
$result = $stmt->execute();    
if ($result === FALSE) {
    die("Error: " . $stmt->error);
}
// AFTER CLICKING DELETE GO TO LINK
// NA HET DRUKKEN VAN DELETE GA NAAR LINK
header("Location: http://localhost:8080/Website/admin.php");

// CLOSE CONNECTION AND STATEMENT
// SLUIT CONNECTIE EN STATEMENT
$stmt->close();
$conn->close();
?>

Upvotes: 0

Views: 62

Answers (2)

FanaticD
FanaticD

Reputation: 1467

This behavior is probably caused because when you enter the page, you are not submitting $_POST["search"] and therefore script enters else branch that selects only data from table person which by all the evidence does not contain column street_address.

You are then trying to print it out anyway, which is where undefined index issue occurs.

EDIT: As the topic progressed, OP bumped into another problem - when he deletes record from person, there are remaining records in tables cv and address.

In fact you need to execute three DELETE sql queries in total. Not one.

First of all, you have to identify records in cv table and address table and delete them, then you delete the person record:

DELETE FROM address WHERE address_id='$addressIDForPerson'

Where $addressIDForPerson is value of person_address column in record from person table that you are trying to delete.

Second step would be to remove the record from cv table, again identified by value of column in person table. 

DELETE FROM cv WHERE cv_id='$cvIDForPerson'

Where $cvIDForPerson is value of person_cv column in record from cv table that you are trying to delete.

And finally, you delete the recordf rom person

DELETE FROM person WHERE person_id=`$personID`

Where $personID identifies the person you are trying to delete.

Upvotes: 2

White Feather
White Feather

Reputation: 2783

In your query:

SELECT * 
FROM `person` 
LEFT JOIN `address` 
LEFT JOIN `cv` 
WHERE CONCAT(`person_firstname`, `person_lastname`, `address_street`, `address_housenumber`, `address_zipcode`, `address_city`, `address_state`, `person_email`, `person_phonenumber` ) LIKE '%".$valueToSearch."%'";

I do not see any ON clause for the JOINS.

You should put those clauses.

Upvotes: 0

Related Questions