How would I go about displaying this is in a table from MySQL with PHP?

[Diamons]

Ok, basically, this is how it works.

 

I have 2 files.

 

search.htm <--- search for whatever

results.php  <--- results shown from database

 

Now, there is one column from about 8. I want the data on results.php to be shown in ascending order for that one table, no matter what they search for in search.htm

 

Sorry if I'm not being clear, I'm not really familiar in this territory, and pardon using my age as an excuse, but I'm nearly 14. So, I'm kind of wondering ^^;

 

Again, let me clarify.

 

On search.htm, people search under 8 tables. results.php displays these results. However, on results.php, I want the results to be shown in ascending order for 2 tables. I want the data to be organized in ascending order from these two tables.

 

Hope I was clear, thank you.

[vijayfreaks]

Hi..

write ur sql qry.. and on which field you want ordering..?

-vijay

[Diamons]

Ok, I'll show you rather.

 

http://gunzrevolution.com/addweapon/search.htm

 

That's where you search. Once you press Search, a bunch of results come up. I want all of them to be ordered chronologically by the Level Requirement. So for example, the lowest Level Requirement entries will be first, then the higher ones, and so on.

[darkfreaks]

please post the php code here so we can help 

[Diamons]

<table width="927" border="2" cellpadding="5" cellspacing="0" bordercolor="#000000" id="navvylist">
<tr>
  <td width="126"><b>Name</b></td>
<td width="86"><b>Category</b></td>
<td width="36"><b>Level Req.</b></td>
<td width="47"><b>Weight</b></td>
<td width="47"><b>Damage</b></td>
<td width="37"><b>Delay</b></td>
<td width="116"><b>Price</b></td>
<td width="288"><b>Description</b></td>
</tr>
<tr>
<td>
<? 
$hostname = "##############";
$username = "##############";
$password = "##############";
$usertable = "##############";
$dbName = "###############";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database");
?>
<?
//error message (not found message)begins
$XX = "You need to search for an entry, or no matching queries were found.";
//query details table begins
$query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50");
while ($row = @mysql_fetch_array($query))
{
$variable2=$row["name"];
$variable3=$row["category"];
$variable4=$row["level"];
$variable5=$row["weight"];
$variable6=$row["damage"];
$variable7=$row["delay"];
$variable8=$row["price"];
$variable9=$row["description"];
$variable10=$row["description"];
//table layout for results

print ("<tr>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("<td>$variable6</td>");
print ("<td>$variable7</td>");
print ("<td>$variable8</td>");
print ("<td>$variable9</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>

[darkfreaks]

in your query try adding

 

ORDER BY 'level'

[Diamons]

Where exactly would that go? Sorry, I'm really not comfortable messing with PHP <-> MySQL. This isn't my field of expertise :-\

[darkfreaks]

<?php $query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50 ORDER BY 'level' "); ?>

[johlwiler]

ok

 

1. is your sql query actually working?

$query = mysql_query("SELECT * FROM details WHERE $metode LIKE '%$search%' LIMIT 0, 50");

 

You should set it up to concatenate assuming that $metode is a php variable and $search is a php variable.

It should look more like this.

$query = mysql_query("SELECT * FROM details WHERE ".$metode." LIKE '%".$search."%' LIMIT 0, 50");

 

notice the quotes and the periods around the variables.

 

2nd I think you are creating to much work for your self by setting each row to a variable then printing it. What I would recommend is create what you want it to look like frist with just plan HTML then add your php in to the while loop.

 

while ($row = @mysql_fetch_array($query))

{

echo "<tr>

echo "<td>".$row["name"]."</td>;

echo "<td>".$row["category"]."</td>;

echo "<td>".$row["level"]."</td>;

echo "<td>".$row["weight"]."</td>;

echo "<td>".$row["damage"]."</td>;

echo "<td>".$row["price"]."</td>;

echo "<td>".$row["description"]."</td>;

echo "</tr>";

}

 

 

or you could also do it something like this. I think of this more as an ASP style but either way some people prefer it.

 

while ($row = @mysql_fetch_array($query))

{

?>

<tr>

<td><?=$row["name"] ?></td>

<td><?=$row["category"] ?></td>

<td><?=$row["level"] ?></td>

<td><?=$row["weight"] ?></td>

<td><?=$row["damage"] ?></td>

<td><?=$row["price"] ?></td>

<td><?=$row["description"] ?></td>

</tr>

 

<?

}

 

In that method basically you can set out any normal html css you want and then print/echo the php stuff were you want it. Fair warning if you use this method in and you have an incrementer in it, make sure it is in the php tags.

 

I see this function at the bottom

if (!$variable1)

{

print ("$XX");

}

 

The problem is I see no where else a $varaible1 so of course it is going to return true no matter what you mysql is. Just because you didn't set it any where else.

 

A better way would be to check $query. So somewhere after

$query = mysql_query("SELECT * FROM details WHERE ".$metode." LIKE '%".$search."%' LIMIT 0, 50");

 

 

do something like this instead

if (!$query)

{

print ("$XX");

}

 

I would tell you to do it before the while loop.

 

By checking $query in this manor you will get something based directly off of what your results are from the database. If there are no results it will print $XX.

 

Also on the same note I don't see $metode defined anywhere. How does your mysql statement know what is supposed to go there if it is not defined somewhere else in php?

 

 

3. Now after all of that depending on how many results you could possibly have you may want to work out a paging method. Think of this when you search google you don't get all 2 million results on one page when searching php. but if you are going to max out at 15 items no matter what it is not a big deal. I feel you need to get everything else working first before you even think of moving on to a paging system. I see you have a limit of 50, what I want 51?? Do you feel you will actually have 50 items?

 

4. also if your site is going to be connecting to the database alot from other pages it may be helpful if you put

$hostname = "##############";

$username = "##############";

$password = "##############";

$usertable = "##############";

$dbName = "###############";

that type of info in a file called dbase.php or constants.php or something like that and then used an include to use its information. That way you don't have it on every page. It saves space and time.

 

Also I see you state that you are searching 8 diffrent tables. if $metode is supposed to be the table that is being searched you should put $metode where the details is. What you have now you are searching a table named detail, where whatever $mettode is  like $search. The where statement should mention the field in that table you are searching.

 

Also I see you mention something about ordering. You can use an order by before the limit.

go here for more info http://www.w3schools.com/sql/sql_orderby.asp

actually the w3schools has an awesome basic SQL tutorial. that is where I got that from for you.

 

 

I hope that gets you on the right track. I know it kind of jumps around but I saw many issue I felt were important to address.

 

[johlwiler]

Hey after retrying you search box with a few diffrent words I did get some results so I don't know if you updated it or something but I still get at the very top a line that says

You need to search for an entry, or no matching queries were found.

So I would recommend addressing that. Also one thing I usally do is keep my actual SQL in a varible

so you have

$sql ="SELECT * FROM details WHERE ".$metode." LIKE '%".$search."%' LIMIT 0, 50"

$query = mysql_query($sql);

 

that way if you have a site where you will use the same SQL from time to time you can store that variable somewhere else and call it. into your querys.

 

I deal a lot with php and mysql in a more advanced environment. Actually I am more into SQL database design and management more than php.

 

Good luck in your endeavorers and I hope you get this working right the way you need it.