Return sql query as array

Question

I'm using jqueryui and its Autocomplete plugin. It use a json to extract items.

I want to modify it so that items will be extracted from my db.

Here is how items should be :

$items = array(
"Great <em>Bittern</em>"=>"Botaurus stellaris",
"Great2 <em>Bittern</em>"=>"Botaurus stellaris 2"
);

How to make an sql query that extract data from a table and write it like the code above into the php file ?

Table : customer
id_customer | name_customer | country_customer

I want that array produce id_customer => name_customer

To give you the exact code, we would need to know what your database schemas looks like. What are your field names? — hookedonwinter
– hookedonwinter, Commented Aug 2, 2010 at 0:28
It seems that i made an error with posting a new message in place of a comment. Anyway, message above edited (thanks for admin by the way :) ) — HW1
– HW1, Commented Aug 2, 2010 at 0:45
Do your id's have HTML formatting (<em>) on them? You shouldn't have that on that DB. — NullUserException
– NullUserException, Commented Aug 2, 2010 at 0:52

Felix Kling · Accepted Answer · 2010-08-02 00:56:28Z

6

The query is just:

SELECT id_customer, name_customer FROM customer

and you can generate the array like so (assuming you are using MySQL):

$items = array();

$result = mysql_query($sql);
while(($row = mysql_fetch_assoc($result))) {
    $items[$row['id_customer']] = $row['name_customer'];
}

References: MySQL SELECT syntax, mysql_query(), mysql_fetch_assoc()

edited Aug 2, 2010 at 0:56

answered Aug 2, 2010 at 0:51

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user408266 · Accepted Answer · 2010-08-02 03:18:31Z

-1

<?php
   //Use mysql_connect for connect to a Db

   $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
   if (!$link) {
       die('Could not connect: ' . mysql_error());
    }

   // Select a DB
   $db_selected = mysql_select_db('db_name', $link);
   if (!$db_selected) {
      die ('Can\'t use dbame_n : ' . mysql_error());
   }

   //Build a query
   $sql = "SELECT id_customer, name_customer FROM customer";

   //Send de query to db  
   $result = mysql_query($sql);
   if (!$result) {
       die('Invalid query: ' . mysql_error());
   }

   // Initialize Array
   $arr_customers = array();
   while(($row = mysql_fetch_assoc($result))) {
       $arr_customers[$row['id_customer']] = $row['name_customer'];
   }

   // convert to JSON
   $json = json_encode($arr_customers);

   // Send to JqueryUI

   echo $json;
   exit();
    ?>

answered Aug 2, 2010 at 3:18

user408266

1

1 Comment

Your Common Sense Over a year ago

I wonder what would JqueryUI do with all these 'Could not connect: ' if happens

Collectives™ on Stack Overflow

Return sql query as array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related