Creating Specific Array format from Database

[AceTutor]

Greetings - I am a newbie to this forum and to PHP and would appreciate assistance in formatting script to a specific format required by my application. Others may recognize the script from the data content. It is for “Smart Search”.

Here is the required format of the array from the original application
[php]

<?php $products = array( 'Avocado' => array('image' => 'assets/images/fruits/avocado.jpg', 'description' => 'The avocado is a dense, evergreen tree, shedding many leaves in early spring.', 'link_url' => 'apple.html'), 'Banana' => array('image' => 'assets/images/fruits/banana.jpg', 'description' => 'Bananas are fast-growing herbaceous perennials arising from underground rhizomes.', 'link_url' => 'banana.html'), /* --Ditto-- */ /* --Ditto-- */ ); ?>

[/php]

Notice that I require one array $products and that it lists various items (fruit) providing details of name, image, description, url details for each item.

Now I want to create the same array format from my database and herein lies my problem.

I can replicate and make this work for the first row of my table but I don’t know how to loop through all valid items from my query.

Here is my code:
[php]

<?php require_once('../../../../Connections/db_link.php'); ?> <?php mysql_select_db($database_db_link, $db_link); $query_products = "SELECT * FROM products WHERE Online = 1 "; $products = mysql_query($query_products, $db_link) or die(mysql_error()); $row_products = mysql_fetch_assoc($products); $totalRows_products = mysql_num_rows($products); $products = array( $row_products['Name'] => array('image' => $row_products['Image'], 'description' => substr($row_products['Description'],0,120), 'link_url' => "/details.php?d=".$row_products['ProductID']), ); mysql_free_result($products); ?>

[/php]

As you can see in the above code - I would appreciate help to be able to loop through line 12 using a do . . .while or for . . . next, whatever, to read all valid items into this one array call products.

If you can provide a working example - or links to where I may glean a valid answer - it would be much appreciated.

Thank you in anticipation

Kind regards

AceTutor

[Redscouse]

wrap this in a while loop…
[php]$products = array(
$row_products[‘Name’] => array(‘image’ => $row_products[‘Image’], ‘description’ => substr($row_products[‘Description’],0,120), ‘link_url’ => “/details.php?d=”.$row_products[‘ProductID’]),
);[/php]

using this…
[php]$row_products = mysql_fetch_assoc($products);[/php]

like so…
[php]while($row = mysql_fetch_assoc($products)) {
$row_products[$row[‘Name’]] = array(
‘image’ => $row[‘Image’],
‘description’ => substr($row[‘Description’],0,120),
‘link_url’ => “/details.php?d=”.$row[‘ProductID’]
);
}
[/php]

More importantly, stop using mysql.
Use mysqli or pdo instead!

Red

[AceTutor]

Greetings again and thank you Redscouse for your comments.

My apologies for the delay in responding. I have discovered that I have been asking the wrong question in wanting to seek a way to create acceptable code for use with “Smart Search”. Smart Search used an external file as a data source in its demonstration version and the above array listing of fruit items was what I was seeking to make. I have wasted good time seeking to mimic the demo without understanding how this data was being used.

Through courtesy of another helper I have been able to not only ask the right question - but in doing so now have a very satisfying solution.

For anyone searching for the same outcome, of dynamically generating a listing from a database for “Smart Search”, here is the solution:

[php]

<?php require_once('../../../../Connections/db_link.php'); $q = $_GET['q']; mysql_select_db($database_db_link, $db_link); $query_products = "SELECT ProductID, Name, Image, Description FROM products WHERE Online = 1 AND Price > 1 AND Name LIKE '%$q%' ORDER BY Name "; $result = mysql_query( $query_products, $db_link ) or die(mysql_error()); $totalRows = mysql_num_rows($result); $final = array('header' => array(), 'data' => array()); $final['header'] = array( 'title' => 'Product Search', 'num' => $totalRows, 'limit' => 10 ); if ( $totalRows > 0 ) { while ( $row_products = mysql_fetch_assoc($result) ) { $final['data'][] = array( 'primary' => $row_products['Name'], 'secondary' => substr($row_products['Description'],0,52).' . . .', 'image' => $row_products['Image'], 'url' => "/details.php?d=".$row_products['ProductID'] , 'onclick' => "location.href='$link_url" ); } } header('Content-type: application/json'); echo json_encode(array($final)); exit( ); ?>

[/php]

ALSO
Part of the delay in responding has been your comment Redscouse!

More importantly, stop using mysql. Use mysqli or pdo instead!

I have started to take up your challenge and am trying to get my head around PDO connections.

If you could point me in the right direction for beginners tutorials - on how I can understand the basics - it would be much appreciated. As mentioned at the start - I am very much a newbie.

Again - I hope this posting is useful to others as well, and thanks for your valued comments

Blessings

AceTutor

[Redscouse]

Greetings again and thank you Redscouse for your comments.
Part of the delay in responding has been your comment Redscouse!
I have started to take up your challenge and am trying to get my head around PDO connections.

If you could point me in the right direction for beginners tutorials - on how I can understand the basics - it would be much appreciated. As mentioned at the start - I am very much a newbie.

You’re welcome, happy to help
I’m also pleased you’re making the change. More often than not this valuable piece of advice is ignored, so well done you! 8)

[member=71845]JimL[/member] has a pretty detailed tutorial on PDO here.

Hope that helps,
Red