i want show record expired. show last 1 record expired.
the code contains pages. test.php , test1.php
test.php code is:
<?php include("database/db.php"); // check connection if($link === false){ die("error: not connect. " . mysqli_connect_error()); } @$result = mysqli_query($link,"select * advertiser,company_information,advertisement,ads_date 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;
the problem override variables ($name etc.) in while loop , print them once instead of printing them in every iteration of 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 in test1.php:
<html> <head> <title> advertisement information</title> </head> <body> <table border="1"> <?php echo $htmltable; ?> </table> </body> </html> edit:
second problem query you're using wrong. listing tables in from clause no join condition you're performing cartesian join (i.e., matching each record each table other records other tables). instead, should specify how these tables should joined, preferably using modern explicit join syntax :
select * advertisement 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 expiry_date < curdate() 
Comments
Post a Comment