Merging two PHP scripts puzzler

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bifter
Forum Newbie
Posts: 2
Joined: Sun Jan 04, 2009 9:41 am

Merging two PHP scripts puzzler

Post by bifter »

I'm fairly new to PHP.

I've set up an AJAX autocomplete using jQuery. Currently this looks up the info from a php script (below) the array is entered directly in the PHP script - it works but is not ideal.

Code: Select all

<?php
$q = strtolower($_GET["q"]);
if (!$q) return;
$items = array(
"Quentin <em>Tarantino</em>"=>"1",
"Christopher <em>Nolan</em>"=>"2",
"Joel <em>Coen</em>"=>"3",
"Ethan <em>Coen</em>"=>"4"
);
 
foreach ($items as $key=>$value) {
    if (strpos(strtolower($key), $q) !== false) {
        echo "$key|$value\n";
    }
}
?>
So I did a bit of research and figured out how I can extract the info that I need from my MySQL db and present this in an array.

Code: Select all

<?php
 
include 'config.php';
include 'opendb.php';
 
$query  = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "Name :{$row['first_names']} <br>" .
         "Surname : {$row['surname']} <br>" .
         "ID : {$row['id']} <br><br>";
}
 
include 'closedb.php';
?>
Both work fine separately but when I try and merge the two I can't seem to get it to work. I want the array in the first script to be provided from the MySQL table.

Here is my latest non functioning attempt...

Code: Select all

<?php
include 'config.php';
include 'opendb.php';
 
$query = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
if (!$query) return;
 
while($items = array(
   "{$row['first_names']}"."{$row['surname']}"=>"{$row['id']}",
));
 
foreach ($items as $key=>$value) {
    if (strpos(strtolower($key), $query) !== false) {
        echo "$key|$value\n";
    }
    
include 'closedb.php';
}
I've been semi-blindly twiddling stuff about and thinking I've got it but am having no joy :banghead:

I really don't have enough experience to know if I'm just a small syntax wrong or are completely out of the ballpark.

If anyone spots an obvious mistake (or 2 or probably more!) I'd really appreciate the help/feedback.

Many thanks in advance
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

Re: Merging two PHP scripts puzzler

Post by msurabbott »

I see a few mistakes, let me just point them out first..

While the while loop appears to have.. 'correct syntax' that is not what they are used for.. The main issue there though is that you are never fetching the rows from the result set so you never actually have access to the columns data..

I really dont know what you are attempting to accomplish with

Code: Select all

if (strpos(strtolower($key), $query) !== false)
but i dont see it being necessary..

You never close your { for the foreach loop at the bottom. The first iteration through it is including the closedb.php and ending execution.

With that being said this is what I have come up with.. UNTESTED:

Code: Select all

<?php
include 'config.php';
include 'opendb.php';
 
$query = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
if (!$query) return;
 
// Iterate through the result rows
while($row = mysql_fetch_array($result) {
    // Add each users name to the array with key matching their id from the database
    $items[$row['id']] = $row['first_names'] . " " . $row['surname'];
}
 
// Iterate through the array you just populated
foreach ($items as $key=>$value) {
    // Will print id|first_name surname
    echo "$key|$value\n";
}
 
include 'closedb.php';
?>
This though, is still verbose for what you need, that same goal can be achieved with:

Code: Select all

<?php
include 'config.php';
include 'opendb.php';
 
$query = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
if (!$query) return;
 
// Iterate through the result rows
while($row = mysql_fetch_array($result) {
    // Add each users name to the array with key matching their id from the database
    echo $row['id'] . "|" . $row['first_names'] . " " . $row['surname'];
}
 
include 'closedb.php';
?>
Both solutions are UNTESTED but should be a good starting point...
bifter
Forum Newbie
Posts: 2
Joined: Sun Jan 04, 2009 9:41 am

Re: Merging two PHP scripts puzzler

Post by bifter »

Many thanks for the concise help.

I tried this code as it is but had no luck. I found out a bit more info that could help.
I really dont know what you are attempting to accomplish with

Code: Select all

if (strpos(strtolower($key), $query) !== false)
but i dont see it being necessary..
I tried removing this line from the original script to see what effect it had. It appears to remove the erroneous lines in the autocomplete. eg if you hit "k" it removes all the elements that don't have a "k" in it. When I took out the line I get the whole list as a drop down even though when they don't include the letter. That said, I could see how it may not apply to the finished script once it is pulling the results from the MySQL table.
You never close your { for the foreach loop at the bottom. The first iteration through it is including the closedb.php and ending execution.
Oh yeah, oops :oops:
With that being said this is what I have come up with.. UNTESTED:

Code: Select all

<?php
include 'config.php';
include 'opendb.php';
 
$query = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
if (!$query) return;
 
// Iterate through the result rows
while($row = mysql_fetch_array($result) {
    // Add each users name to the array with key matching their id from the database
    $items[$row['id']] = $row['first_names'] . " " . $row['surname'];
}
 
// Iterate through the array you just populated
foreach ($items as $key=>$value) {
    // Will print id|first_name surname
    echo "$key|$value\n";
}
 
include 'closedb.php';
?>
This though, is still verbose for what you need, that same goal can be achieved with:

Code: Select all

<?php
include 'config.php';
include 'opendb.php';
 
$query = "SELECT first_names, surname, id FROM writers";
$result = mysql_query($query);
if (!$query) return;
 
// Iterate through the result rows
while($row = mysql_fetch_array($result) {
    // Add each users name to the array with key matching their id from the database
    echo $row['id'] . "|" . $row['first_names'] . " " . $row['surname'];
}
 
include 'closedb.php';
?>
Both solutions are UNTESTED but should be a good starting point...
I tried both of these and they didn't seem to return any results.

I dug in the docs for the autocomplete and found this.
For remote data: When the user starts typing, a request is send to the specified backend ("my_autocomplete_backend.php"), with a GET parameter named q that contains the current value of the input box and a parameter "limit" with the value specified for the max option.

A value of "foo" would result in this request url: my_autocomplete_backend.php?q=foo&limit=10

The result must return with one value on each line. The result is presented in the order the backend sends it.
So, I guess that the $q has some significance to make it work. Now I'm a little confused as to whether $q in the 1st original script is = to $query in the 2nd original script? Or do I need to use both? I hope this is making sense...
Post Reply