CODEWITHSUNDEEP
phpmysqldatabase
Imran Khan
Imran Khan

Reputation: 31

Select Expired Records From MySQL Tables using PHP

I want to show all those record which is Expired. but this show only the last one record which is expired.

The code contains pages. test.php and test1.php

test.php code is:

<?php


include("database/db.php"); 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}


@$result = mysqli_query($link,"select * from advertiser,company_information,advertisement,ads_date
 WHERE Expiry_Date < CURDATE()");

if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result))
 {
  $dateid = $row['Ads_date_id'];
$name=$row['Name'];
$cnic= $row['CNIC'];
$compname = $row['Company_Name'];
$adstitle = $row['Ads_Title'];
$startdate = $row['Starting_Date'];
$expiredate = $row['Expiry_Date'];



}
}
else
{

  echo '<script type="text/javascript">'; 
echo 'alert("No Record Found");'; 
echo 'window.location.href = "SingleCustomer.php";';
echo '</script>';
}
mysqli_close($link);

?>
<?php
include("test1.php");
?>

test1.php code is:

<html>
<head>
    <title> Advertisement Information</title>
</head>

<body>
<table border="1">
<tr>
<td><?php echo $name; ?></td>

<td> <?php echo $cnic; ?></td>

<td> <?php echo $compname; ?></td>

<td> <?php echo $adstitle; ?></td>

<td> <?php echo $startdate; ?></td>

<td> <?php echo $expiredate; ?></td>

<td> <?php echo $dateid; ?> </td>
</tr>

</table>

</body>
</html>

i have multiple tables in MYSQl.

1.advertiser
2.company_information
3.advertisement
4.ads_date

Advertiser:

CREATE TABLE `advertiser` (
  `Adv_id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL,
  `F_Name` char(20) NOT NULL,
  `Address` varchar(40) NOT NULL,
  `CNIC` int(13) NOT NULL,
  `Contact` int(11) NOT NULL,
  `Monthly_fee` varchar(10) NOT NULL,
  `Region` varchar(10) NOT NULL,
  `Reg_date` varchar(10) NOT NULL,
  PRIMARY KEY (`Adv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

2.Company_information:

CREATE TABLE `company_information` (
  `Company_id` int(11) NOT NULL AUTO_INCREMENT,
  `Company_Name` varchar(20) NOT NULL,
  `Company_Contact` int(11) NOT NULL,
  `Company_Address` varchar(30) NOT NULL,
  PRIMARY KEY (`Company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

3.advertisement:

CREATE TABLE `advertisement` (
  `Ads_id` int(11) NOT NULL AUTO_INCREMENT,
  `Adv_id_id` int(11) NOT NULL,
  `Company_id` int(11) NOT NULL,
  `Ads_Title` varchar(20) NOT NULL,
  `Ads_Description` varchar(40) NOT NULL,
  `Ads_Image` varchar(50) NOT NULL,
  PRIMARY KEY (`Ads_id`),
  KEY `Adv_id` (`Adv_id_id`),
  KEY `Company_id` (`Company_id`),
  CONSTRAINT `Adv_id` FOREIGN KEY (`Adv_id_id`) REFERENCES `advertiser` (`Adv_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `advertisement_ibfk_1` FOREIGN KEY (`Company_id`) REFERENCES `company_information` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

4.ads_date:

CREATE TABLE `ads_date` (
  `Ads_date_id` int(11) NOT NULL AUTO_INCREMENT,
  `Ads_id` int(11) NOT NULL,
  `Starting_Date` varchar(30) NOT NULL,
  `Expiry_Date` varchar(30) NOT NULL,
  PRIMARY KEY (`Ads_date_id`),
  KEY `Ads_id` (`Ads_id`),
  CONSTRAINT `Ads_id` FOREIGN KEY (`Ads_id`) REFERENCES `advertisement` (`Ads_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 1

Views: 446

Answers (1)

Mureinik
Mureinik

Reputation: 312329

The problem is that you override your variables ($name etc.) in the while loop and then print them just once instead of printing them in every iteration of the loop. E.g.:

In test.php:

$htmlTable = '';
while($row = mysqli_fetch_array($result)) {
    $htmlTable .= '<tr><td>' . row['Ads_date_id'] .
    . '</td><td>' . $row['Name']
    . '</td><td>' . $row['CNIC'];
    . '</td><td>' . $row['Company_Name'];
    . '</td><td>' . $row['Ads_Title'];
    . '</td><td>' . $row['Starting_Date'];
    . '</td><td>' . $row['Expiry_Date']
    . '</td></tr>';

}

And then in test1.php:

<html>
<head>
    <title> Advertisement Information</title>
</head>

<body>
<table border="1">
<?php echo $htmlTable; ?>

</table>

</body>
</html>

EDIT:
The second problem is that the query you're using is wrong. By just listing all the tables in the from clause with no join condition you're performing a Cartesian join (i.e., matching each record from each table with all the other records from the other tables). Instead, you should specify how these tables should be joined, preferably by using the modern explicit join syntax :

SELECT *  
FROM   advertisement a
JOIN   company_information ci ON ci.company_id = a.company_id
JOIN   advertiser ar ON ar.adv_id = a.adv_id
JOIN   ads_date ad ON ad.ads_id = a.ads_id
WHERE  expiry_date < CURDATE()

Upvotes: 2

Related Questions