Incremental Search

MySQL database
#1

Hi all, I need some assistance if possible. I have an online radio station and I have a script tht serches my song database. I want to get it to where if someone types in a letter such as “A” it will only display artists that start with “A” and if the type in B, C, D, E it will only display the artists of those letters, The way it is now if you type in any letter it will display all artists from A-Z, I don’t want it to do this, I want it to do what I explained above here is my current code dould someone tell me how to change/modify it to do what I explained above? Thanks in advance

[code]

<?php $servername = "localhost"; $username = "alloldie_samdb"; $password = "********"; $dbname = "alloldie_samdb"; echo "Artist Quick Search"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT DISTINCT artist FROM songlist ORDER BY artist"); $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach($stmt->fetchAll() as $k=>$v) { //echo $v[] = $v; echo "".implode($v).""; //echo $data[] = $v; } } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; echo ""; ?>

[/code]

#2

Sorry if I did not post the code right I’m new to this

#3

You are missing a WHERE clause. You said when they type in, but you have a dropdown input, is there also a text field?

#4

When the page opens it has a drop down box that reads " Artist Quick Search" if you click the down arrow, it lists all the artsts from A-Z and if you type in any letter it also displays all artists from A-Z here is a link to the page:

http://www.alloldies247.com/srtest3.php

I want it to do an alphabetical search for each individual letter you type in like this type in “A” and it lists:

1 A Flock Of Seagulls - I Ran (So Far Away
2 A Taste Of Honey - Sukiyaki
3 A-Ha - Take On Me
4 Aaron Neville - Tell It Like It Is

The same for each letter you type in displaying only the artists from whatever letter you type in. (see pic) Thank you in advance

Screenshot (84).png
Screenshot (84).png1366×768 133 KB

#5

[php]$stmt = $conn->prepare(“SELECT DISTINCT artist FROM songlist WHERE artist LIKE ? ORDER BY artist”);
$stmt->execute(array("{$_GET[‘q’]}%"));[/php]

Something like this then.

#6

I will insert it into the code and let you know what happens

#7

Nope still does the same thing but thanks for trying, hopefully I will get it working

#8

Post your most up to date code then.

If you have phpmyAdmin or something like that run the query,

[php]SELECT DISTINCT artist FROM songlist WHERE artist LIKE ‘A%’ ORDER BY artist[/php]

And see what result set you get returned.

#9

Latest updated code:

<?php $servername = "localhost"; $username = "alloldie_samdb"; $password = "^^^^^^^"; $dbname = "alloldie_samdb"; echo "Artist Quick Search"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT DISTINCT artist FROM songlist WHERE artist LIKE ? ORDER BY artist"); $stmt->execute(array("{$_GET['q']}%")); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach($stmt->fetchAll() as $k=>$v) { //echo $v[] = $v; echo "".implode($v).""; //echo $data[] = $v; } } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; echo ""; ?>

and when I ran the query you told me to I got this result (see pic)

Screenshot (86).png
Screenshot (86).png1366×768 95.7 KB

#10

Change the letter to something else. The query looks to be running correctly.

#11

Yep what ever letter I change the query to it displays only artists from that letter no i just have to figure out how to do it in my code lol now when i try the page you can’t type in a letter you can only use the arrow in the drop down box and all it displays is artists from the letter “A” ???

http://www.alloldies247.com/test12.php

#12

You want two separate queries, One that will pull all for the drop down, and one that will show only what you want. The select should only use the first query, the page, if the query is used, should show the second query.

#13

Can I add that to my code? If so, how would I do it (what would the code be)

#14

So how would I add a 2nd query to accomplish what I want this search to do?

#15

I’ll give you some things to search for, so you can see how to do it.

I would load the select using Ajax when the page loaded. You can pass a get or post parameter to a page so it knows what to return.

Default the general query if nothing specific is requested, but this means separating the html from the php code.

#16

I think I might have another way to go, tell me if it is possible, if you click the link below, it takes you to my playlist page at the top there is a search box and what ever letter you type in it will only display from the brgining of the list (listing all artists from A-Z) but under it you see search letters and what ever letter you click, it takes you to the artist refering to only that letter (like I want it to do. My question would it be possible to use the code from the search letter and somehow implement it into the search box code? I have included the search code so you can look it over and see if what I want to do is possible here is the link to my playlist page first of all:

http://www.alloldies247.com/samphp/web/playlist.php

code for search box:

[php]BEGIN:SEARCH -->

		  <div id="search">
		<form method="get" action="playlist.php" name="searchParameters">
		  <?php InputText('search', $search, '',20); ?>
		<input type="submit" value="Go" name="B1" /> 
		Display <?php InputCombo('limit', $limit, 25, '5,10,25,50,100', "", "document.forms.searchParameters.submit();"); ?>results[/php]

code for search letters:

[php]


<input <?php echo "0 - 9" == $character? "id='activeCharacter'" : "";?> type=“submit” name=“character” class="characterButton"value=‘0 - 9’/>
						<?php
						for($charVal = ord('A');$charVal <= ord('Z'); $charVal++) {
							$c = chr($charVal);
							echo "<td>";
							echo "<input ".($character == $c? "id='activeCharacter'" : "")." type='submit' name='character' class='characterButton' value='$c' onclick='document.forms.searchParameters.search.value=\"\"' />";
							echo "</td>";
						}
						?>
					</tr>
				</tbody>
			</table>


			</form>

			<br />
		</div>
		<!-- END:SEARCH -->[/php]

would it be possible to use the code from the search letters in the search box search option?

#17

I’ll look it over shortly, but yes that is feasible, that is how a search box works.

Of you have the ability to edit your posts, the code tags are like html, the opening one goes before the code and the closing after. Can you fix that or do you need me to?

#18

You can if you don’t mind lol and I want to keep the search letter option on the page, i just want to implement the search letter code to work with the search box option. Sorry about all of this I’m in the learning process lol

#19

Very basic API example:

[php]<?php

$action = isset( $_GET[‘action’] ) ? $_GET[‘action’] : null;

switch ( strtolower( $_GET[‘action’] ) )
{
case ‘search’:
search( $_GET[‘value’] );
break;
default:
// your homepage
}

function search( PDO $pdo, $val ){
$stmt = $pdo->prepare(“SELECT DISTINCT artist FROM songlist WHERE artist LIKE ? ORDER BY artist”);
$stmt->execute(array("$val%"));
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}[/php]

Separate page. You would use this for ajax calls. Parameters are action and value.

so, example.com?action=search&value=a

JQuery $.get

#20

Stupid question… Do I put that code in my playlist.php file? I’m new to all of this stuff

Sponsor our Newsletter | Privacy Policy | Terms of Service