Member Avatar for laura301019

$dbQuery="select id, name from artists where name like '$keyword%' order by name asc limit 15";
$dbResult=mysql_query($dbQuery);
echo mysql_num_rows($dbResult)."\n";
while ($dbRow=mysql_fetch_array($dbResult)) {

// for each artist, return the number of albums belonging to that artist
// use a separate query here to obtain the number
// return the number after the artist's name in the echo statement, separated with a '^' (1b)

echo $dbRow["id"]."^".$dbRow["name"]."\n";

Have this code and as you can see i have to write another dbquery that will display the number of albums an artist has. I also have to modify this piece of code


function showArtists() {
if (ajaxObject.readyState==4) {
var artistsArray=ajaxObject.responseText.spl…
var numArtists=artistsArray[0];
var htmlStr="";
for (var i=1; i<=numArtists; i++) {
var artistDetails=artistsArray.split("^")…
htmlStr+="<div onmouseover='javascript:suggestOver(this… ";
htmlStr+="onmouseout='javascript:sugge… ";
htmlStr+="onclick='javascript:setArtis… ";

// add the number of albums in brackets after the artist's name in the next line (1b)
htmlStr+="class='suggest_link'>" + artistDetails[1] + "</div> ";

Anyone know how i write this, ive tried a few things but when i try it on the webpage it stops the ajax auto fill when typing.

  • 3 Contributors
  • 2 Replies
  • 438 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by Biiim

Dani AI

Generated

Both 's symptom (AJAX autofill stops) and the SQL hints from and point to two separate issues: how the album count is produced on the server, and how the client parses the server response. The aggregation idea is correct, but emitting a custom, caret-delimited string is fragile (artist names can contain the delimiter and a single JS error will kill the whole autocomplete). Returning structured JSON and using a parameterized query avoids both parsing fragility and SQL injection, and also makes the client code easier to reason about.

Example server-side pattern (PHP + PDO): prepare a safe query, fetch rows as associative arrays, and emit JSON.

<?php
// prepare/execute with bound parameter, then return JSON
$stmt = $pdo->prepare(
  "SELECT a.id, a.name,
     (SELECT COUNT(*) FROM albums al WHERE al.artistid = a.id) AS album_count
   FROM artists a
   WHERE a.name LIKE :kw
   ORDER BY a.name ASC
   LIMIT 15"
);
$stmt->execute([':kw' => $keyword . '%']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
header('Content-Type: application/json; charset=utf-8');
echo json_encode($rows);

Client-side pattern: parse JSON, escape output to prevent XSS, and build suggestion items from explicit properties (no fragile array indexing).

var list = JSON.parse(xhr.responseText);
function esc(s){ return String(s).replace(/&/g,'&amp;').replace(/</g,'&lt;'); }
list.forEach(function(it){
  html += "<div class='suggest_link' onmouseover='suggestOver(this)' onmouseout='suggestOut(this)' onclick='setArtist(" + it.id + ")'>";
  html += esc(it.name) + " (" + it.album_count + ")";
  html += "</div>";
});

Notes and troubleshooting: set the response Content-Type header, check the browser console and Network tab for JS or JSON parsing errors, and escape names before inserting HTML. For very large result sets prefer a single JOIN+GROUP aggregation for performance (as suggested); the correlated subquery above is fine for small pages or the 15-row limit. Also migrate away from deprecated mysql_* calls to PDO or mysqli and always use bound parameters.

Recommended Answers

All 2 Replies

Member Avatar for rch1231

Hello,

What you are looking for is something like this:

select artist, count(artist)
from mytable
group by artist
Member Avatar for Biiim 
SELECT id, name, count(*) as `albums` FROM artists
LEFT JOIN albums ON artists.id = albums.artistid
WHERE artists.name like '$keyword%'
GROUP BY artists.id
ORDER BY artists.name ASC
LIMIT 15;

the ajax doesn't look like the complete code either so hard to advise on that, if it's stopping things that worked before there is likely a javascript error in the console.

If you change to the query above, making `albums` your albums table and with the correct id fields it looks like you want to change this line:

echo $dbRow["id"]."^".$dbRow["name"]."\n";

to this

echo $dbRow["id"]."^".$dbRow["name"]."^".$dbRow["albums"]."\n";

then in the javascript you access it with "artistDetails[2]"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.