I really hope you can help me, I'm struggling a lot with this.
I have set up a MySQL database with three tables and foreign keys:
Merchants
Issues (FK MerchantID to Merchants > MerchantID)
Updates (FK IssueID to Issues > IssueID)
I want my users to search for a merchant - if their search term is close/matches the merchant's name, the Merchant's details are output first, then it automatically outputs all issues related to that merchant, and beneath each issue, automatically outputs all updates related to that issue.
I have tried the below code which I found on another website, and tried modifying it but have inevitably broken it. This code duplicates the same issue for each update.
I.e.
Merchant's details
Issue 1 -
Update 1
Issue 1 -
Update 2
Code:
<?php
$query = $_GET['query'];
$min_length = 3;
if(strlen($query) >= $min_length){
$query = htmlspecialchars($query);
$query = mysql_real_escape_string($query);
$raw_results = mysql_query("SELECT * FROM Merchants JOIN Issues using(MerchantID) JOIN Updates using(IssueID) WHERE Name LIKE '%$query%'") or die(mysql_error());
if(mysql_num_rows($raw_results) > 0){
while($results = mysql_fetch_array($raw_results)){
echo "Merchant Name: ".$results['Name']."<br />Phone: ".$results['Phone']."<br />Email: <i>".$results['Privacy']."</i> ".$results['Email'];
echo "<p></p><b>Issue ID:</b> ".$results['IssueID']."<br /><b>Date:</b> ".$results['IssueDate']."<br /><b>Deal ID:</b> ".$results['DealID']."<br /><b>Deal Date <i>(valid from)</i>:</b> ".$results['DealDate']."<br /><b>Deal Title:</b> ".$results['DealTitle']."<br /><b>Category:</b> ".$results['Category']."<br /><b>Issue:</b><p></p>".$results['Issue']."<p><hr /></p><b>Solution:</b><p></p>".$results['Solution'];
echo "<p></p><b>Update Date:</b> ".$results['UpdateDate']."<br />Update: ".$results['Update'];
// posts results gotten from database(title and text) you can also show id ($results['id'])
}
}
else{
echo "No results";
}
}
else{
echo "Minimum length is ".$min_length;
}
?>
Could you run the following SQL query directly in your database (using phpMyAdmin or something similar?) and see if the results match what you are looking for?
SELECT m.*, u.*, i.*
FROM Merchants AS m, Updates AS u, Issues AS i
WHERE m.Name LIKE '%$query%'
AND i.MerchantID = m.MerchantID
AND u.IssueID = i.IssueID
When doing that, replace %$query% by a search term included in your database.
Obviously I couldn't test it, but that should be getting you somewhere hopefully =)
Thank you very much, I tried what you suggested and it resulted in the same merchant being output twice. It seems that for each update an issue has, it outputs the merchant and issue details times that number. Also it's outputting the update before the issue. It should be Merchant details, then all related Issues to that merchant, and under each issue, all related updates for that issue.
At this time, MerchantID 5 has one issue and two updates:
MerchantID = 5
IssueID = 5
In the Updates table, UpdateID 5 and 7 have been give the IssueID of 5 (thus hopefully linking them to Issues > IssueID 5).
Does it look like I've set up the database wrong? Or is it just a matter of displaying it correctly on a PHP page with PHP and MySQL? If you need any more information please let know, and thank you very much for taking time to help me.
No, that's exactly the output I was guessing you'd get. It is now a matter of getting your PHP structured in a way that it outputs the HTML you want.
Personally I'd go with using a merchant object (if you're using OO?) and filling that with two associative arrays, one for updates and one for issues. You can then loop through the issue array and find the updates related to it and output that, moving on to the next issue.
If you need more information let me know and I'll see if I can work out some code for you. Won't be until tomorrow evening though.
Right, I've put something together. I don't know if it does what you want, but it outputs a table of 3 columns (merchants, issues and updates) and should output roughly thesame as you have in your picture.
PHP Code:
// Set merchant, issue and update value to blank. $merchant = ""; $issue = ""; $update = "";
// Loop through all the results in the MySQLi $result set. You'll obviously have to // load it with $result = $mysqli->query($sql) or something first. while($r = $result->fetch_array()) { // Start each result on a new row $display .= "<tr>";
// If the merchant name is not thesame as the last one, // set $merchant to this merchant name and fill cell with // name and other details. Otherwise, just echo empty cell. if($r['merchant_name'] != $merchant) { $merchant = $r['merchant_name']; $display .= "<td>$r['merchant_name']<br />$r['merchant_phone']</td>"; } else { $display .= "<td> </td>"; } // If the issue name is not thesame as the last one, // set $issue to this issues name and fill cell with // name. Otherwise, just echo empty cell. if($r['issue_name'] != $issue) { $issue = $r['issue_name']; $display .= "<td> </td><td>$r['issue_name']</td>"; } else { $display .= "<td> </td>"; }
// Update is always displayed in each row if exists. $display .= "<td>$r['update_name']</td></tr>"; }
$display .= "</table>";
Hope it's a bit of help
Regards,
Martin
Last edited by Thyrosis; 11-27-2012 at 10:35 PM..
Reason: added comment
I'm sorry, I kinda gave up checking this thread as I figured people had lost interest in it. I wish I had checked again before paying a freelancer to solve my problems...
I'm really worried that I've been duped out of some money. I paid a freelancer to write me some code to do this. The problem is that when I search for a record that I know should return some results, I get error #3 (known_issues.php) - telling me it can't find a matching record.
I was wondering if you'd be able to have a quick look at the code to see if anything looks like it's breaking? The person I hired says he cannot replicate the problem - he's sent me screenshots to 'prove' this. So either he's lying and sent me shoddy code, or it's something I did?
I don't know if he's bugged the code so I have to go back and pay him more money?
db_con.php
Code:
<?php
$con = mysql_connect("localhost","myusername","mypassword");
if (!$con)
{
die('Could not be connected: ' . mysql_error());
}
mysql_select_db("my_db", $con);
function cleanQuery($string)
{
if(get_magic_quotes_gpc()) // prevents duplicate backslashes
{
$string = stripslashes($string);
}
if (phpversion() >= '4.3.0')
{
$string = mysql_real_escape_string($string);
}
else
{
$string = mysql_escape_string($string);
}
return $string;
}
?>
known_issues.php
Code:
<?php include("/var/www/vhosts/mysite.co.uk/httpdocs/password_protect.php"); ?>
<?php include('db_con.php'); ?>
<form method="post" action="search.php" enctype="multipart/form-data">
<input type="search" name="search_name" /> <input type="submit" name="save" value="Search" />
<br /><label class="small">Enter a merchant's name - partial search permitted.</label>
</form>
<p></p>
<i class="error">
<?php if (isset($_REQUEST['status']) and $_REQUEST['status'] == 1) { ?>
Thank You!
<?php }else{ ?>
<?php } ?>
<?php if (isset($_REQUEST['status']) and $_REQUEST['status'] == 2) { ?>
From must be greater than To!
<?php }else{ ?>
<?php } ?>
<?php if ( isset($_GET['error']) and $_GET['error'] == 1 ) { ?>
I'm sorry, you've entered an invalid search term. Please enter at least one letter or number to search the database correctly.
<?php } ?>
<?php if ( isset($_GET['error']) and $_GET['error'] == 2 ) { ?>
I'm sorry, you've entered an invalid search term. Please enter at least one letter or number to search the database correctly.
<?php } ?>
<?php if ( isset($_GET['error']) and $_GET['error'] == 3 ) { ?>
I'm sorry, your search term does not match a record in the database. If you are aware of an issue that you feel should be included in the database of known issues, please report this using the contact form - the issue will be reviewed and if necessary the database will be updated accordingly.
<?php } ?>
</i>
I haven't looked at this in any great depth, but I notice $IssueID is only mentioned once. Where is it defined?
PHP Code:
$queryU = mysql_query("SELECT * FROM Updates WHERE IssueID = '$IssueID'");
Perhaps it should be $Issue but this is just a guess.
__________________
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
Validate your HTML and CSS
* On line 8 he sets $search = cleanQuery($search);
* On line 36 he builds the query WHERE Name LIKE '%.$search_name.%'"
So, on line 8 $search should be replaced by $search_name, or on line 36 you should replace $search_name with $search. If he can't reproduce the error, he's got register_globals turned on, and you've got it turned off.
I would also replace that first query build on line 36 to the following:
PHP Code:
$query = mysql_query("SELECT * FROM Merchants WHERE Name LIKE '%{$search_name}%'");
Firstly because I don't know what those dots are doing there. Secondly because I've got a habbit of using {} around variables in double-quoted (" instead of ') strings.
To get back to the issue at hand, the error=3 flag is given of when if($num_rows!=0) on line 39 resolves to the else clause, so when num_rows is either 0 or FALSE. I'm guessing the above changes will solve the issue.
If it does, tell the freelancer to stop relying on register_globals being turned on, it's "DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0." (to quote PHP.net)
// Edit: I just realised that the forum doesn't show linenumbers. Paste it into a code-editor which does if you don't fancy counting the rows
Last edited by Thyrosis; 11-30-2012 at 12:26 PM..
Reason: realised [PHP] doesn't show line-numbers
Awesome, your fix worked! I changed the query and changed $search_name to $search.
Unfortunately the updates aren't being printed under the issues.
The freelancer even sent me screenshots which showed this and he didn't pick up on it. I'm guessing now he's got his money, he's not interested.
At several points, even from the start he said he fully understood the brief then produced work that clearly indicated he didn't understand at all.
If I may impress up you once more, although feel free to tell me to bugger off, does the code to output my results look like it should work? Or has the freelancer used archaic code again or just plain wrong code?
I've asked the freelancer this too so if you're done with this then that's ok, it's very comforting to know that there's someone out there that actually knows what they're talking about.
I think I'm missing a closing table statement somewhere... But it's really difficult to judge just by looking at the code alone. Could you run the script and then output the HTML source code? Also a screenshot of the resulting page would be nice.
It might actually be a browser/CSS/styling issue, you never know. I am very reluctant to criticize the work of a 'colleague', as it seems valid code (apart from the obvious mishap with the $search_term variable, but that could happen to anyone).
Prior to setting up the output, I created a test HTML file on my desktop with static output results to ensure that it displayed correctly. So I know it can't be an issue with CSS and having checked the markup above, the update tables aren't even being printed to the page.
I'm sorry if I you felt like I was leading you to criticise the freelancer. I only question the code because it's not working as intended, so I'm not getting what I paid for at this moment. I'm just trying to establish if I've been given code that is sound and should work - if so then it's another problem, possibly my meddling, even!
Before you post, read our: 
