[SOLVED] arrange alphabetically

[dare87]

I have a database that is storing a bunch of info.  Right now when I pull all the information down it just pulls and then sorts them in alphabetical order.  What I would like to do is have links at the top of the page…

 

# A B C D E F …

 

Is there a way to make it so that it will pull the Title field and automatically set the first letter to link the # A B C...

 

To see what I want to here:

 

<a href="http://cheats.ign.com/index/playstation-2-cheats.html">IGN</a>

 

I think I need to make an array… but I am very new to this and have done most of my coding by looking at others code.

 

Thanks for all your help

[dare87]

I also forgot to say if there is not a way to make it automatic is there a way that I can place the A B C D ... and then make them links that would pull from the database. like the IGN link above.

[dcp]

You could run a query to get the first letter of your title such as

 

$result = mysql_query(SELECT DISTINCT SUBSTR('title',1,1) as title FROM tablename ORDER BY title ASC) or die(mysql_error());

    while ($row = mysql_fetch_array($result)) {

    echo "<a href"linktoanotherpage.php?begins=".$row['title']."\">".$row['title']."</a> | ";

} 

 

 

Your other page would then search for all the items that began with the letter stored in 'begins':

 

$query = "SELECT id, title FROM tablename WHERE title LIKE '".$_GET['begins']."%' ORDER BY title ASC";

$result = mysql_query($query) or die(mysql_error());

    while ($row = mysql_fetch_array($result)) {

    echo "<a href"linktoitem.php?id=".$row['id']."\">".$row['title']."</a><br />";

}

 

 

Doesn't seem too efficient if you have a lot of records.

[dare87]

Here is what I have as of now (see below)

I decided it would be better to just put the A B C D

I would like to make it so that when A is click it would do basically what you said and run the query that would pull all the A’s for the title field.  I again am new to this and don't know what I need to do to make this work.  Thanks for the help and future help

 

		<?php
	// Connect to the database.
	require_once ('../../datemysql_connect.php');

	// The number of pages to display per page.
	$display = 20;

	// Calculate how many pages will be needed.
	// If the number of pages has not been calculated, then it will need to calculated.

	if (isset($_GET['np']))
		$num_pages = $_GET['np'];
	else // Needs to be calculated.
	{
		// Count the number of records in the database.
		$query = "SELECT COUNT(*) FROM datedb ORDER BY title ASC";

		$result = mysql_query ($query);

		$row = mysql_fetch_array ($result, MYSQL_NUM);

		$num_records = $row[0];

		// Calculate the number of pages to use.
		if ($num_records > $display)
			$num_pages = ceil ($num_records / $display);
		else
			$num_pages = 1;
	}

	// Determine in the database to start returning results.
	if (isset($_GET['s']))
		$start = $_GET['s'];
	else
		$start = 0;
	// Make the query.
	$query = "SELECT user_rating AS userrating, title AS title, type1 AS type1, type2 AS type2, type3 AS type3, type4 AS type4, date AS article, supplies AS supplies, cost AS cost, rating AS rating, name AS name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 ORDER BY title ASC LIMIT $start, $display";

	// Run the query.
	$result = @mysql_query ($query);

	// If the query ran w/o error, print out the results.
	if ($result)
	{
		// Table header.
		echo ' <table width="100%" align="center" cellpadding="5">
				<tr><td>Other</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td><td>U</td><td>V</td><td>W</td><td>X</td><td>Y</td><td>Z</td></tr>
</table>

		';

		echo '  <table width="100%" align="center" cellpadding="5">
				<tr>
					<td>

				<table width="100%" align="center" cellspacing="0" cellpadding="5">
				<tr>
					<td align="left"><u><b>Title</b></u></td>
					<td align="left"><u><b>Last Updated</b></u></td>
					<td align="left"><u><b>Rating</b></u></td>
				</tr>';

		// Fetch and print all the records.

		$bg = '#dee4ed'; // Set the background color.

		while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			// Alternate the background color.
			$bg = ($bg == '#dee4ed' ? '#ffffff' : '#dee4ed');

			echo '  <tr bgcolor="' . $bg . '">
						<td align="left"><a href="date_view.php?userrating=' . $row['userrating'] . '&title=' . $row['title'] . '">' . $row['title'] . '</a></td>
						<td align="left">' . $row['date'] . '</td>
						<td align="left"><img src="images/rate' . $row['rating']. '.gif"</td>
					</tr>';							
		}

		// Close the table.
		echo '</table>';

		// Free up the resources.
		mysql_free_result ($result);

		// Make links to other pages, if necessary.
		if ($num_pages > 1)
		{
			echo '<br /><p>';
			// Determine what page the script is on.
			$current_page = ($start / $display) + 1;

			// If it's not the first page, create a previous button.
			if ($current_page != 1)
				echo '<a href="ideas.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';

			// Make all the numbered pages.
			for ($i = 1; $i <= $num_pages; $i++)
				if ($i != $current_page)
					echo '<a href="ideas.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
				else
					echo $i . ' ';

			// If it's not the last page, make a Next button.
			if ($current_page != $num_pages)
				echo '<a href="ideas.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a> ';

			echo '</p>';
		} 
	}
	else
	{
		// If the query did not run successfully, print out an error message.
		echo 'The date database could not be retrieved.';
	}

	// Close the database connection.
	mysql_close();
	?>

[dcp]

You might just modify the query a bit -- check for the presence of a variable (in this case $_GET['first_letter']) and if its there add that bit to the query. (I've added line breaks below that just make it a bit easier for me to read.):

 

----------

// Make the query.

$query = "SELECT user_rating AS userrating, ";

$query .= "title AS title, type1 AS type1, type2 AS type2, type3 AS type3, type4 AS type4, ";

$query .= "date AS article, supplies AS supplies, cost AS cost, rating AS rating, ";

$query .= "name AS name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date ";

$query .= "FROM datedb WHERE approved=1 ";

 

// this little bit checks for the 'first_letter' variable

if (isset($_GET['first_letter'])) { $query .= "AND title LIKE '".$_GET['first_letter']."%' "; }

 

$query .= "ORDER BY title ASC LIMIT $start, $display";

 

----------

 

Then, where you output the letters A, B, C, D, etc. add the links like this:

 

. . . <td><a href="?first_letter=A">A</a></td><td><a href="?first_letter=B">B</a></td><td><a href="?first_letter=C">C</a></td>. . . 

 

and so on.

 

"Other" is a bit problematic with this approach.

 

 

 

 

 

[dare87]

I will try that... looks like it will work... anyother tips or ideas would be great.

 

Thanks

[dare87]

Well I got it to work... I was doing it the other way and decided that it was just to messy.  So I did this instead and It works great.(see below)

 

<?php // Make the query.
	if(isset($_GET['start_letter']))
	{
		if($_GET['start_letter'] == "1")
		{
			$query = "SELECT user_rating AS userrating, title, type1 AS type1, type2 AS type2, type3 AS type3, type4 AS type4, date AS article, supplies						AS supplies, cost AS cost, rating AS rating, name AS name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 and title NOT REGEXP '^[A-Za-z]+' ORDER BY title ASC LIMIT $start, $display";	
		}else{

		$query = "SELECT user_rating AS userrating, title, type1 AS type1, type2 AS type2, type3 AS type3, type4 AS type4, date AS article, supplies AS supplies, cost AS cost, rating AS rating, name AS name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 and title like '" . $_GET['start_letter'] . "%' ORDER BY title ASC LIMIT $start, $display";
		}
	}
	else
	{
	$query = "SELECT user_rating AS userrating, title AS title, type1 AS type1, type2 AS type2, type3 AS type3, type4 AS type4, date AS article, supplies AS supplies, cost AS cost, rating AS rating, name AS name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 ORDER BY title ASC LIMIT $start, $display";
	}
	// Run the query.
	$result = @mysql_query ($query);

	// If the query ran w/o error, print out the results.
	if ($result)
	{
		// Print ABC index.
		echo ' <table width="100%" align="center" cellpadding="5"><tr>';
		echo('<td><a href="ideas.php">ALL</a></td>');
		echo('<td><a href="ideas.php?start_letter=1">#</a></td>');
		for($a = 65; $a <= 65+25; $a++)
		{
			echo('<td><a href="ideas.php?start_letter=' . chr($a) . '">' . chr($a) . "</a></td>");
		}
		echo '</tr></table>'; ?>

[dare87]

I have run into another problem that I cannot solve.  Once you have click on one of the titles it takes you to another page. That page pulls the rest of the information from the database and prints in out for viewing. 

 

The table is as so

 

Field Type Null Default

title varchar(50) Yes

rating int(5) Yes

supplies varchar(400) Yes

date varchar(2000) Yes

cost varchar(500) Yes

user_rating int(5) Yes NULL

name varchar(40) Yes anonymous

approved char(1) Yes 0

date_entered timestamp Yes CURRENT_TIMESTAMP

type1 int(5) Yes

type2 int(5) Yes

type3 int(5) Yes

type4 int(5) Yes

 

 

The type1 2 3 4 how int that are from 0-30

 

I have another table that has

 

Field Type Null Default

type_id int(5) Yes

type varchar(30)

 

 

The type field holds a name like

 

John, Stacey, or Joe

And the type_id holds a int from 0-30

So

John=0

Stacey=1

So on

 

I would like to make it so instead of seeing the 0 1 2… see the name.

 

I have tried adding “type WHERE datedb.type1=type.type_id” to the sql statement but because I am pulling more then just type1 id does not work… I also want it so that if it pulls 0 as the type_id i don't want it to print out on the page. Thanks for your help

 

[dcp]

What's the sql query you are using? You shouldn't be getting more than one type1 id if your query is limited to one record.

[dare87]

well I have type1 type2 type3 type4

they all have values 0-30is. 

0-30is is in another table and they have type_id and type

so 0=none 1=sam... stuff like that.

So i only get type1 to show up if i do that and i want type1-4 to show... unless it is 0...1-30ish showing.

[dcp]

Oh. I see. Ideally, you'd have a third table, something like datedb2type that links the datedb table and the type table. That would make things much simpler.

 

I don't really know what to do here. You might have to query four times for the type. So:

 

-----------------------------

 

$query= "SELECT * FROM datedb INNER JOIN type ON datedb.type1=type.type_id WHERE id = '".$_GET['id']."' ";

//I'm just assuming you have a unique id field as it's not in your field list above

 

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

echo "<p>".$row['title']."<br />";

. . .and whatever you want to output. . .

 

// this will output the text for type1. You can put a similar if statement in below, as well.

if ($row['type1'] != 0) {

    echo "<strong>Type:</strong>".$row['type']."<br />";

}

$query2 = "SELECT type FROM type WHERE type_id = '".$row['type2']."' LIMIT 1";

    $result = mysql_query($query2);

    while ($row = mysql_fetch_array($result)) {

    echo "<strong>Another Type:</strong>".$row['type']."<br />";

}

$query3 = "SELECT type FROM type WHERE type_id = '".$row['type3']."' LIMIT 1";

    $result = mysql_query($query3);

    while ($row = mysql_fetch_array($result)) {

    echo "<strong>Another Type:</strong>".$row['type']."<br />";

}

$query4 = "SELECT type FROM type WHERE type_id = '".$row['type4']."' LIMIT 1";

    $result = mysql_query($query4);

    while ($row = mysql_fetch_array($result)) {

    echo "<strong>Another Type:</strong>".$row['type']."<br />";

}

 

}

[dare87]

What I did

<?php


if ($results)
	{	
		while($row = mysql_fetch_array($results, MYSQL_ASSOC))
		{
			$arraytype[$row['type_id']] = $row['type'];

		}
		unset($results);
		unset($row);
	}
	unset($query);

//later on down the code

$type1 = $arraytype[$row['type1']];
$type2 = $arraytype[$row['type2']];
$type3 = $arraytype[$row['type3']];
$type4 = $arraytype[$row['type4']];

?>

//later on down the code

<?php echo "$type1 $type2 $type3 $type4"; ?>

 

That worked... but THANKS for all the ideas and help... I'm sure I will have more questions... I should have leared more before I took on such a big task... o well