modify hard coded array to database pull

Report · Oct 18, 2010

Hello all,

I'm new to the array stuff, and am having problems finding a tutorial or instructions on how to modify a hard coded array to something pulled from a database. I can only find tutorials on how to do it hardcoded. They keep saying it is possible, but never say how to do it. The original tutorial that I'm trying to learn which brought up this question can be found at: http://www.brandspankingnew.net/archive/2007/02/ajax_auto_suggest_v2.html

So as an example, if I've got the database connection already established as:

$con = mysql_connect("localhost", "root", "") or die('Could not connect to server');
mysql_select_db("my_database", $con) or die('Could not connect to database');

With fields as my_database_names, my_database_ratings, and my_database_images; how do I modify the below code to use the database information instead of using the hardcoded array? Any suggestions (or letting me know of where a good tutorial is) would be appreciated.

<?php

/*
note:
this is just a static test version using a hard-coded countries array.
normally you would be populating the array out of a database

the returned xml has the following structure
<results>
<rs>foo</rs>
<rs>bar</rs>
</results>
*/

$aUsers = array(
"Adams, Egbert",
"Altman, Alisha",
"Archibald, Janna",
"Auman, Cody",
"Bagley, Sheree",
"Ballou, Wilmot",
"Bard, Cassian",
"Bash, Latanya",
"Beail, May",
"Black, Lux",
"Bloise, India",
"Blyant, Nora",
"Bollinger, Carter",
"Burns, Jaycob",
"Carden, Preston",
"Carter, Merrilyn",
"Christner, Addie",
"Churchill, Mirabelle",
"Conkle, Erin",
"Countryman, Abner",
"Courtney, Edgar",
"Cowher, Antony",
"Craig, Charlie",
"Cram, Zacharias",
"Cressman, Ted",
"Crissman, Annie",
"Davis, Palmer",
"Downing, Casimir",
"Earl, Missie",
"Eckert, Janele",
"Eisenman, Briar",
"Fitzgerald, Love",
"Fleming, Sidney",
"Fuchs, Bridger",
"Fulton, Rosalynne",
"Fye, Webster",
"Geyer, Rylan",
"Greene, Charis",
"Greif, Jem",
"Guest, Sarahjeanne",
"Harper, Phyllida",
"Hildyard, Erskine",
"Hoenshell, Eulalia",
"Isaman, Lalo",
"James, Diamond",
"Jenkins, Merrill",
"Jube, Bennett",
"Kava, Marianne",
"Kern, Linda",
"Klockman, Jenifer",
"Lacon, Quincy",
"Laurenzi, Leland",
"Leichter, Jeane",
"Leslie, Kerrie",
"Lester, Noah",
"Llora, Roxana",
"Lombardi, Polly",
"Lowstetter, Louisa",
"Mays, Emery",
"Mccullough, Bernadine",
"Mckinnon, Kristie",
"Meyers, Hector",
"Monahan, Penelope",
"Mull, Kaelea",
"Newbiggin, Osmond",
"Nickolson, Alfreda",
"Pawle, Jacki",
"Paynter, Nerissa",
"Pinney, Wilkie",
"Pratt, Ricky",
"Putnam, Stephanie",
"Ream, Terrence",
"Rumbaugh, Noelle",
"Ryals, Titania",
"Saylor, Lenora",
"Schofield, Denice",
"Schuck, John",
"Scott, Clover",
"Smith, Estella",
"Smothers, Matthew",
"Stainforth, Maurene",
"Stephenson, Phillipa",
"Stewart, Hyram",
"Stough, Gussie",
"Strickland, Temple",
"Sullivan, Gertie",
"Swink, Stefanie",
"Tavoularis, Terance",
"Taylor, Kizzy",
"Thigpen, Alwyn",
"Treeby, Jim",
"Trevithick, Jayme",
"Waldron, Ashley",
"Wheeler, Bysshe",
"Whishaw, Dodie",
"Whitehead, Jericho",
"Wilks, Debby",
"Wire, Tallulah",
"Woodworth, Alexandria",
"Zaun, Jillie"
);

$aInfo = array(
"Bedfordshire",
"Buckinghamshire",
"Cambridgeshire",
"Cheshire",
"Cornwall",
"Cumbria",
"Derbyshire",
"Devon",
"Dorset",
"Durham",
"East Sussex",
"Essex",
"Gloucestershire",
"Hampshire",
"Hertfordshire",
"Kent",
"Lancashire",
"Leicestershire",
"Lincolnshire",
"Norfolk",
"Northamptonshire",
"Northumberland",
"North Yorkshire",
"Nottinghamshire",
"Oxfordshire",
"Shropshire",
"Somerset",
"Staffordshire",
"Suffolk",
"Surrey",
"Warwickshire",
"West Sussex",
"Wiltshire",
"Worcestershire",
"Durham",
"East Sussex",
"Essex",
"Gloucestershire",
"Hampshire",
"Hertfordshire",
"Kent",
"Lancashire",
"Leicestershire",
"Lincolnshire",
"Norfolk",
"Northamptonshire",
"Northumberland",
"North Yorkshire",
"Nottinghamshire",
"Oxfordshire",
"Shropshire",
"Somerset",
"Staffordshire",
"Suffolk",
"Surrey",
"Warwickshire",
"West Sussex",
"Wiltshire",
"Worcestershire",
"Durham",
"East Sussex",
"Essex",
"Gloucestershire",
"Hampshire",
"Hertfordshire",
"Kent",
"Lancashire",
"Leicestershire",
"Lincolnshire",
"Norfolk",
"Northamptonshire",
"Northumberland",
"North Yorkshire",
"Nottinghamshire",
"Oxfordshire",
"Shropshire",
"Somerset",
"Staffordshire",
"Suffolk",
"Surrey",
"Warwickshire",
"West Sussex",
"Wiltshire",
"Worcestershire",
"Durham",
"East Sussex",
"Essex",
"Gloucestershire",
"Hampshire",
"Hertfordshire",
"Kent",
"Lancashire",
"Leicestershire",
"Lincolnshire",
"Norfolk",
"Northamptonshire",
"Northumberland",
"North Yorkshire",
"Nottinghamshire"
);

$input = strtolower( $_GET['input'] );
$len = strlen($input);
$limit = isset($_GET['limit']) ? (int) $_GET['limit'] : 0;

$aResults = array();
$count = 0;

if ($len)
{
for ($i=0;$i<count($aUsers);$i++)
{
   // had to use utf_decode, here
   // not necessary if the results are coming from mysql
   //
   if (strtolower(substr(utf8_decode($aUsers[$i]),0,$len)) == $input)
   {
    $count++;
    $aResults[] = array( "id"=>($i+1) ,"value"=>htmlspecialchars($aUsers[$i]), "info"=>htmlspecialchars($aInfo[$i]) );
   }

   if ($limit && $count==$limit)
    break;
}
}

if (isset($_REQUEST['json']))
{
header("Content-Type: application/json");

echo "{\"results\": [";
$arr = array();
for ($i=0;$i<count($aResults);$i++)
{
   $arr[] = "{\"id\": \"".$aResults[$i]['id']."\", \"value\": \"".$aResults[$i]['value']."\", \"info\": \"\"}";
}
echo implode(", ", $arr);
echo "]}";
}
else
{
header("Content-Type: text/xml");

echo "<?xml version=\"1.0\" encoding=\"utf-8\" ?><results>";
for ($i=0;$i<count($aResults);$i++)
{
echo "<rs id=\"".$aResults[$i]['id']."\" info=\"".$aResults[$i]['info']."\">".$aResults[$i]['value']."</rs>";
}
echo "</results>";
}
?>

Report · Oct 19, 2010

Are you planning to do this entirely by hand-coding, or do you want to use Dreamweaver server behaviors?

If you're hand-coding, you should be using MySQLi instead of the old MySQL functions, which are no longer actively maintained. MySQLi requires a minimum of PHP 5 and MySQL 4.1.

You create a MySQLi connection like this:

$conn = new mysqli($host, $username, $password, $db_name);
$sql = 'SELECT * FROM mytable';
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
  // each of the database columns is $row['column_name']
}

See the PHP manual for more details: http://docs.php.net/manual/en/mysqli.query.php.

If you're planning to use the Dreamweaver server behaviors, the following tutorial will show you how to query a database: http://www.adobe.com/devnet/dreamweaver/articles/first_dynamic_site_pt1.html.

Report · Oct 19, 2010

Thank you so much. I was planning on hand coding it because my objective is to learn, not to have dreamweaver do it for me, so I will check out the link :

http://docs.php.net/manual/en/mysqli.query.php.

LOL, I thought I was just starting to understand MySQL, and now I've got to start learning MySQLi. I hope there is not too much different between the two. Thanks again.