| need to change PHP code to include a new table in MySQL
php query code, need help with ading results from new table in MySQL |
KG2RG
msg:3335361 | 10:59 pm on May 9, 2007 (gmt 0) |
Hello all,
I need a little help on this PHP results back end script page. I have a database with 3,ooo,ooo files and with 3 tables. I also have a shell script that loges onto a .gov site, downloads the updated database, and overwrites/populates the MySQL tables with the updated data every day via cron. The query works fine, but I added a new table, and would like to display the data from the new table UNDER the original results. I will post the php code below, but here are a few things that may help. There are 2 search fields, 1 for callsign, and the other for zip codes. I am only concerned with the callsign query. Also, this code is formated to query all 3 of the original 3 tables and return the most updated data. The original 3 tables are named am, en, and hd. When a search query for callsign is being done, it gets all the data from the 3 tables, am, en, and hd. Then it displays the results. I would like the results from my newly added table, named hamwave_logs, to be displayed just under the original results. All 3 tables plus my new table have a field named callsign. Here is the code:
<table>
<tr>
<td>
<?php
$callsearch = substr(strtoupper(trim($_POST["call"])),0,6);
$zip = substr(trim($_POST["zip"]),0,9);
$rowlimit = 0;
#
if ( ($callsearch == '') and ($zip == '') ) {
$callsearch = substr(strtoupper(trim($_GET["call"])),0,6);
$zip = substr(trim($_GET["zip"]),0,9);
$rowlimit = ereg_replace('[^0-9]','',substr(trim($_GET["rowlimit"]),0,4));
$calllike = ereg_replace('[^A-Z0-9]','',substr(strtoupper(trim($_GET["calllike"])),0,6));
}
#
$callsearch = ereg_replace('[^A-Z0-9]','',$callsearch);
$zip = ereg_replace('[^0-9]','',$zip);
#
if ($rowlimit == 0) {
$rowlimit = 1000;
}
#
if ($calllike!= '') {
$query = "select en.callsign, full_name, address1, city, state, zip, class, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.callsign like \"$calllike%\"";
} else {
if ($callsearch!= '') {
$query = "select en.callsign, full_name, address1, city, state, zip, class, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.callsign=\"$callsearch\"";
} else {
if ($zip!= '' ) {
$query = "select en.callsign, full_name, address1, city, state, zip, class, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.zip like \"$zip%\" order by en.callsign limit $rowlimit";
}
}
}
#
if ( $query!= '' ) {
$link = mysql_connect("localhost", "myusername", "mypassword") or die ("Could not connect to database. Sorry, dude. 72");
mysql_select_db("call_log") or die("Could not select database");
$result = mysql_query($query) or die("Query failed");
if ( mysql_num_rows($result) == 1) {
$row = mysql_fetch_row ($result);
$row[6] = translate_class($row[6]);
print "<b>$row[0]</b><br>$row[1]<br>$row[2]<br>$row[3],$row[4] $row[5]<br> <br>Class: $row[6]";
if ( $row[7]!= '' ) {
print"<br>Former Callsign: $row[7]";
}
} else {
if ( mysql_num_rows($result) > 1 ) {
$rowcount = mysql_num_rows($result);
print "Records found: $rowcount ";
if ( $rowcount == $rowlimit) {
print "(maximum record limit)";
}
print "<table>";
for ($rowcounter = 1; $rowcounter <= $rowcount; $rowcounter++) {
$row = mysql_fetch_row ($result);
$row[6] = translate_class($row[6]);
print "<tr>";
#print "<b>$row[0]</b>,$row[1],$row[2],$row[3],$row[4] $row[5], $row[6]<br>";
print "<td><b><font size=2>$row[0]</b></td><td><font size=2>$row[1]</td><td><font size=2>$row[2]</td><td><font size=2>$row[3]</td><td><font size=2>$row[4]</td><td><font size=2>$row[5]</td><td><font size=2>$row[6]</td>";
print "</tr>";
}
print "</table>";
} else {
print "<br>No records found...";
}
}
#
mysql_free_result($result);
mysql_close($link);
} else {
print "<br>Please enter some criteria...";
}
#
?>
</P></FONT></FONT>
<p></font></td>
</tr>
</table>
Thank you! [edited by: eelixduppy at 12:15 am (utc) on May 10, 2007]
[edit reason] removed specifics and some code [/edit]
|
mcibor
msg:3335839 | 12:59 pm on May 10, 2007 (gmt 0) |
OK, first things first: 1. apart from ereging values you put into db, I would also use mysql_real_escape_string: $zip = mysql_real_escape_string($zip);
...etc for $callsearch, $calllike and $rowlimit 2. you know there is a function elseif? It will make your code a bit clearer. 3. Back to your main query:
Before </table> I would query the second table, then retrieve the data from it, and at last close the </table> I wouldn't put the query into the main. To do that you would need to use UNION, which would slower your query. My method is a bit faster. Hope this helps you
michal
|
|
|
