Hello
Let me thank you in advance for all the help you guys can provide, unfortunately php and sql is not my strong however i have been assigned to this task and nobody here in my office seems to understand how php works, i have a small amount of knowledge but is very tiny compared to the things you guys probably know haha.
well my issue is this, we have this script that we want to update since the php script was written over 10 years ago it hasn’t been maintained or let alone updated to the newer code, i understand things change through the years and since this code is old it does not run on our newer IIS server with PHP manager, the script in question works like this
user types a query on web interface->query connects to database server->result is shown on the generated table ->database info comes from 2 different tables (at least on my understanding).
so with that said we have different scripts one contains the database /password /username information, the other one contains an adming login and the S7 script which is the one pulling the information from the database.
I would like you guys to help me figure out this, im not asking for any of you to rewrite the code for me, no, i want to learn as well from this experience so what im asking is for your help looking at the script the way i have it so far and tell me if it’s good or not. so far when i query on the web interface i get no results from the database, i have a feeling that the database is not set properly on the script. any input will be appreciated.
thanks everybody
[php]
body,table,tr,td {
font-family:Helvetica;
font-size:14px;
}
<?php
/*
Newark Public Library
Song Index Search Script
V 0.07
Last Updated: 3/6/05
*/
include "query_cleaner.php";
//Define the SQL database login information
include "info.php";
$resultcount = 0;
//attempt to connect to database server
$connectionInfo = array( "Database"=>"SongFile", "UID"=>"dvelasquez", "PWD"=>"*67Excelsior#");
sqlsrv_connect( $myServer, $connectionInfo)
or
die("Unable to connect to database server. If this problem persists, please contact an administrator
".sqlsrv_errors);
//select our database
//mssql_select_db('SongFile'); OLD VERSION
$ip = $_SERVER['REMOTE_ADDR'];
$para = substr($ip,0,5);
$parab = substr($ip,0,7);
$parac = substr($ip,0,6);
if($para == '10.10' or $parab = '209.212' or $parac = '199.95')
{
$internal = 1;
}
else
{
$internal = 0;
}
$starttime = time();
$parsedsearcher = parseAll($_POST['Search']);
$parsedsearcher2 = parseAll2($_POST['Search']);
$statsquery = 'INSERT INTO dwinship.STATS VALUES (30,'.$starttime.",\"".$parsedsearcher."\",'".$ip."',".$internal.',0)';
sqlsrv_query($statsquery);
if ($_POST['type'] == 'all') {
///BEGIN Exact Phrase\\\
//query the database for an exact match to the search term
$isQueryBad = checkForBad($parsedsearcher);
if ($isQueryBad === 0)
{
$query="SELECT TITLE,SUBALTTITL,CALLNUMBER,PAGENUMBER,MYKEY FROM dwinship.SINDEX3
where title like \"%".$parsedsearcher2."%\" or subalttitl like
\"%".$parsedsearcher2."%\" or title like \"%".$parsedsearcher."%\" or subalttitl like
\"%".$parsedsearcher."%\" order by
title";
$result = sqlsrv_query($query);
$resultcount=0;
//output any results
echo '
| Title [/ Composer] |
Subtitle/Source/Additional Information |
Call Number |
Page |
';
while ($row = sqlsrv_fetch_array($result))
{
if (isset($row["SUBALTTITL"]) === FALSE)
{
$row["SUBALTTITL"] = " ";
}
//==========================
//clavis interop hack
if(substr($row["CALLNUMBER"],0,1) == "7")
{
$holder = $row["CALLNUMBER"];
if(strpos($holder,"(") !== false)
{
$holder = str_replace(substr($holder,strpos($holder,"(")),"",$holder);
}
$holder = str_replace(" ","%20",$holder);
$row["CALLNUMBER"] = "".$row["CALLNUMBER"]."";
}
//==========================
echo "
| ".$row["TITLE"]." |
".$row["SUBALTTITL"]." |
".$row["CALLNUMBER"]." |
".$row["PAGENUMBER"]." |
";
$resultcount=$resultcount+1;
}
//clear result variable to reduce memory use on the server
sqlsrv_cancel($result);
///End Exact Phrase\\\
}
}
elseif ($_POST['type'] == 'any') {
/*NOTE: To allow the most likely results to be placed on top, an exact phrase search is done before the any-of-the-words spider is called on */
///BEGIN Exact Phrase\\\
//query the database for an exact match to the search term
$parsedsearcher = parseAll($_POST['Search']);
$isQueryBad = checkForBad($parsedsearcher);
$gotResult = 0;
if ($isQueryBad === 0)
{
$query="SELECT TITLE,SUBALTTITL,CALLNUMBER,PAGENUMBER FROM dwinship.SINDEX3 where title like \"%".$parsedsearcher."%\" or subalttitl like \"%".$parsedsearcher."%\" order by title"; $result = sqlsrv_query($query);
$resultcount=0;
//output any results
$output = '
| Title [/ Composer] |
Subtitle/Source/Additional Information |
Call Number |
Page |
';
while ($row = sqlsrv_fetch_array($result))
{
if (isset($row["SUBALTTITL"]) === FALSE)
{
$row["SUBALTTITL"] = " ";
}
//==========================
//clavis interop hack
if(substr($row["CALLNUMBER"],0,1) == "7")
{
$holder = $row["CALLNUMBER"];
if(strpos($holder,"(") !== false)
{
$holder = str_replace(substr($holder,strpos($holder,"(")),"",$holder);
}
$holder = str_replace(" ","%20",$holder);
$row["CALLNUMBER"] = "".$row["CALLNUMBER"]."";
}
//==========================
$output = $output."
| ".$row["TITLE"]." |
".$row["SUBALTTITL"]." |
".$row["CALLNUMBER"]." |
".$row["PAGENUMBER"]." |
";
$resultcount=$resultcount+1;
}
//clear result variable to reduce memory use on the server
sqlsrv_cancel($result);
if ($resultcount != 0)
{
echo($output);
$gotResult = 1;
}
///End Exact Phrase\\\
}
///Begin Any Of The Words Search\\\
//form any-of-the-words query
$term=$_POST['Search'];
//generates an upercase-only version of the search term (necessary since the PHP filter used later on is case sensitive)
$uterm=strtoupper($term);
//breaks the original search term into its individual words
$terms=cleanQuery($uterm);
if ($terms[0] >= 0 and $isQueryBad === 0)
{
$substring=0;
//finds the number of words stored in the $terms array
$count=count($terms);
//defines the base query
$query2="SELECT TITLE,SUBALTTITL,CALLNUMBER,PAGENUMBER FROM dwinship.SINDEX3 where";
while ($substring < $count){
if ($substring >= 1) {
//adds data into query for the latter words of the search term
$query2=$query2." or title like \"% ".$terms[$substring]." %\" or title like \"% ".$terms[$substring]."\" or title like \"".$terms[$substring]." %\" or subalttitl like \"(".$terms[$substring]." %\" or subalttitl like \"% ".$terms[$substring].")\" or subalttitl like \"% ".$terms[$substring]." %\"";
$substring=$substring+1;
} else {
//adds data into the query for the first word of the search term
$query2=$query2." title like \"% ".$terms[$substring]." %\" or title like \"% ".$terms[$substring]."\" or title like \"".$terms[$substring]." %\" or subalttitl like \"(".$terms[$substring]." %\" or subalttitl like \"% ".$terms[$substring].")\" or subalttitl like \"% ".$terms[$substring]." %\"";
$substring=$substring+1;
}
}
//adds on the order by title directive to alphabeticly organize results by title
$query2=$query2."GROUP BY title,subalttitl,callnumber,pagenumber order by title,subalttitl";
$result2 = sqlsrv_query($query2);
//organize results
$k = 0;
$anyrcount = 0;
while ($row2 = sqlsrv_fetch_array($result2))
{
$i = 0;
$i2 = 0;
$relevance = 0;
//checks to be sure that the result was not picked up by the first spidering process
if (strpos(" ".$row2["TITLE"],$uterm) == 0 and strpos(" ".$row2["SUBALTTITL"],$uterm) == 0)
{
foreach ($terms as $term1)
{
$g = 0;
for ($c = 0; $c + strlen($term1) <= strlen($row2["TITLE"]); $c = $c + 1)
{
$i = strpos(" ".$row2["TITLE"],$term1,$g);
if ($i != FALSE)
{
$relevance = $relevance + 1;
$g = $i + 1;
if ($g + strlen($term1) > strlen($row2["TITLE"]))
{
break;
}
}
else
{
break;
}
}
$g = 0;
for ($d = 0; $d + strlen($term1) <= strlen($row2["SUBALTTITL"]); $d = $d + 1)
{
$i = strpos(" ".$row2["SUBALTTITL"],$term1,$g);
if ($i != FALSE)
{
$relevance = $relevance + 1;
$g = $i + 1;
if ($g + strlen($term1) > strlen($row2["SUBALTTITL"]))
{
break;
}
}
else
{
break;
}
}
}
$relevant[$k]["title"] = $row2["TITLE"];
$relevant[$k]["subtitle"] = $row2["SUBALTTITL"];
$relevant[$k]["callnumber"] = $row2["CALLNUMBER"];
$relevant[$k]["pagenumber"] = $row2["PAGENUMBER"];
$relevant[$k]["rel"] = $relevance;
if ($relevant[$k]["subtitle"] == "")
{
$relevant[$k]["subtitle"] = " ";
}
$k = $k + 1;
$resultcount=$resultcount+1;
$anyrcount = $anyrcount + 1;
}
}
sqlsrv_cancel($result2); //clear result variable to reduce memory use on the server
function cmp($a, $b)
{
if ($a["rel"] == $b["rel"])
{
return 0;
}
else
{
return ($a["rel"] > $b["rel"]) ? -1 : 1;
}
}
if ($anyrcount > 0)
{
usort($relevant,"cmp");
if ($gotResult === 1)
{
$output = "";
}
else
{
$output = '
| Title [/ Composer] |
Subtitle/Source/Additional Information |
Call Number |
Page |
';
}
$f = 0;
while ($f < $k)
{
//==========================
//clavis interop hack
if(substr($relevant[$f]["callnumber"],0,1) == "7")
{
$holder = $relevant[$f]["callnumber"];
if(strpos($holder,"(") !== false)
{
$holder = str_replace(substr($holder,strpos($holder,"(")),"",$holder);
}
$holder = str_replace(" ","%20",$holder);
$relevant[$f]["callnumber"] = "".$relevant[$f]["callnumber"]."";
}
//==========================
$output = $output."
| ".$relevant[$f]["title"]." |
".$relevant[$f]["subtitle"]." |
".$relevant[$f]["callnumber"]." |
".$relevant[$f]["pagenumber"]." |
";
$f = $f + 1;
}
if ($f != 0)
{
echo($output);
}
}
///End Any Of The Words Search\\\
}
}
//output error if no result is found
if ($resultcount==0) {
echo "";
if ($isQueryBad === 1)
{
include "filteredwords.php";
if ($parsedsearcher === " ")
{
echo " Error: Invalid Search Term (Space at Beginning).
Click here to return to the search form ";
}
else if ($parsedsearcher === "")
{
echo " Error: No search term entered.
Click here to return to the search form";
}
else if (strlen($parsedsearcher) === 1)
{
echo " Error: One character search term. Please enter a longer search term.
Click here to return to the search form";
}
foreach ($filteredwords as $w)
{
if (strtoupper($parsedsearcher) === $w)
{
echo " Error: Illegal search word: ".$w." without other words in query. Please add more words to your query.
Click here to return to the search form ";
break;
}
}
}
else //no results
{
echo "Sorry, no results were found that matched your query of ".$_POST['Search'].".
";
$s = 0;
//generate "helpful" error based off common problems with Any of the Words
if ($_POST['type'] == 'any')
{
include "filteredwords.php";
//breaks query into terms for analysis of failure
$terms1 = explode(" ",strtoupper($parsedsearcher));
foreach ($filteredwords as $w)
{
foreach ($terms1 as $tm) //walks query array
{
if ($tm === $w) //if a word matches one known to be filtered
{
if ($s === 0) //if this is the beginning of the error addenda
{
echo "Note: The following words were automaticly removed from the second phase of the Any of the words Search: ".$w;
$s = 1;
}
else //if it isn't the beginning of the error addenda
{
echo ", ".$w;
}
}
}
}
}
if ($s == 1)
{
echo "
"; //adds extra line break if a "helpful" error was thrown
}
echo "Click here to return to the search form"; //Prints out link back to the main form
}
echo " |
"; //closes out error box
}
else
{
echo "
"; //ends table if results were found
}
$endtime = time();
$runtime = $endtime - $starttime;
$statsquery2 = "UPDATE dwinship.STATS SET runtime = ".$runtime." WHERE time = ".$starttime." and query = \"".$parsedsearcher."\"";
sqlsrv_query($statsquery2);
$statsquery3 = "UPDATE dwinship.STATS SET resultcount = ".$resultcount." WHERE time = ".$starttime." and query = \"".$parsedsearcher."\"";
sqlsrv_query($statsquery3);
echo ("Query took ".($runtime + 1)." second(s) to run. Query received on ".strftime("%A, %B %d at %I:%M:%S %p %Z",$starttime)." from an ");
if ($internal == 0)
{
echo "external referrer. ";
}
else
{
echo "internal referrer. ";
}
echo($resultcount." result(s) processed by the server.");
?>
[/php]
