PHP - Mysql + Php - Results Per Line

this is my code but i want to have a pice so if their is no results the it will put in a message like "No Results"
but i cant figger it out


<?php
$host="localhost";
$username="xxxxx";
$password="xxxxxx";
$db_name="xxxxxxx";
$tbl_name="Shows";

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name WHERE Month='April'and Year='2011' order by Day asc ";
$result=mysql_query($sql);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">

<?php
while($rows=mysql_fetch_array($result)){
?>

<span class="elevenbold"><? echo $rows['Day']; ?> <? echo $rows['Month']; ?> <br><strong class="elevenboldblue"><? echo $rows['Song']; ?></strong><br><? echo $rows['Data']; ?></span><br><br>


<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

I have a problem displaying the results of a table,

It shows them in one long list and I'd like them to show as 2 or 3.

I had a look around and the most favoured way to this I found was to split the results either by odd/even or using percentages but Im having trouble implementing it into my script.

How would I go about adding the odd/even way to this:

<?php
$query = mysql_query('SELECT * FROM users ORDER BY Username');
while ($row = mysql_fetch_array($query)) {
if ($row['UserID']) 
       ?>
<b>Username:</b> <span class=class1><a href="aeditprofile.php?username=<? echo $row['Username'] ?>"><? echo $row['Username'] ?></a></span><br>
<b>Group:</b> <? echo $row['Level'] ?></color><br /><hr>
<?php
}
?>


Essentially I'm after getting something like this:

Name                           Name                          Name
Group                          Group                         Group

and so on down the page....

I've got a page that displays all my blog entries on a single page. When you click on a specific entry, it pulls the post ID and loads the page specifically. From there, I wanted to have the option to click either onto the next or previous post by finding the next or previous ID in the database.. however, I know that sometimes posts are deleted, etc. so the ID's won't exactly be in order.

What direction should I take with this?

I am trying to join two tables but I am producing no results. I'm convinced that I have entered everything correctly, but clearly I haven't? Can anyone spot my mistake?

<?php
require 'core/memberinit.php';
$member = new Member();
include 'timeago.php';

$memberid = $member->data() ->id;

if(isset($_GET['q'])) {
    $q = html_entity_decode($_GET['q']);

$query2 = DB::getInstance()->query("SELECT c.id c.time as time, c.message as message, c.removed1, c.removed2, 
			m1.member_first_name as firstname1, m1.member_last_name as lastname1, m1.username as username1,  
			m2.member_first_name as firstname2, m2.member_last_name as lastname2, m2.username as username2 
			FROM conversation c
				LEFT JOIN members m1 ON c.member1=m1.id
				LEFT JOIN members m2 ON c.member2=m2.id
			WHERE (c.id=$q OR c.originalid=$q)
			AND ((c.member1=$memberid AND c.removed1='No') 
			OR (c.member2=$memberid AND c.removed2='No'))
			ORDER BY c.id");
		
foreach ($query2->results() as $result2)
{
		echo $result2->firstname1;
		echo $result2->lastname1;
		echo $result2->username1;
		echo timeAgo(strtotime($result2->time));
		echo $result2->message;
		echo $result2->firstname2;
		echo $result2->lastname2;
		echo $result2->username2;
}
?>

Edited by MartynLearnsPHP, 23 November 2014 - 12:55 PM.

I'm having two issues:   (1) The correct mysql query for multiple rows (2) The if/elseif/else to pull data from these rows, and process it based on the row data   I'm only pulling data where A=1 and B=1,2 -- so two possible entries there (call them B1 and B2).

I need a php if statement to choose whether the output of B is one of two urls (b1=google,b2=bing).

The actual script is far more complex, with more than just 1,2 from B. I've stripped all the excesses down to this one if/else issue and the db query. The output php doesn't matter here. And I can add more elseif once this problem is solved.

<?php 
mysql_connect(localhost, $db_username, $db_password);
 @mysql_select_db($db_database) or die("No connection");  

$query = "Select * FROM table WHERE column='stuff' AND parent='1,2' ORDER BY id DESC LIMIT 10";
 $query_result = mysql_query($query);
 $num_rows = mysql_num_rows($query_result);  
 mysql_close();  

?>  

<?php
    for($i=0; $i< $num_rows; $i++){   //start a loop 
    $stuff = mysql_result($query_result, $i, "column");

    $row = mysql_fetch_assoc($query_result, $i);
    if($row['parent'] == 1) {
        $url = 'http://google.com';
    } else {
        $url = 'http://www.bing.com';
    }

?>

My own first attempt at a if/then was 500. I got help on another site to redo it (new code shown here), but the new if/else always show the else (Bing).   It was also at this time that I learned that "1,2" only showed 1.   Hoping that this site is far more friendly than StackOverflow.   .
Edited by kpmedia, 17 December 2014 - 07:22 PM.

Hi all,

In order to teach myself php I am developing a simple cms system to display my graphic design work. Basically I am working on developing a simple portfolio website powered by a custom made CMS.

So far, I can create new posts which are being stored in a MySQL db.

My index page runs a sql query to get the latest posts and sorts them by category.

Currently the results look like this:

Quote

Title1
Category A
Text

Title2
Category A
Text

Title3
Category B
Text

Title4
Category B
Text


The code to generate this looks like this:

$content = mysql_query("SELECT * FROM posts ORDER BY category, date DESC");

while($row = mysql_fetch_array($content))
  {
  echo '<h2 class="post_title">'.$row['title'].'</a></h2>';
  echo '<p class="cat">category: '.$row['category'].'</p>';
  echo '<p class="post_body">'.$row['body'].'</p>';
  echo '<p class="image">image: '.$row['image'].'</p>';
  echo '<p class="lowerlefttext_metainfo">by <span class="lowerlefttext_author">'.$row['author'].'</span> on '.$row['date'].'</p>';
  }

What I would like however is this:

Category A

Title 1
Text

Title 2
Text

Category B

Title 3
Text

Title4
Text

I am not sure how to do this elegantly without having to code an sql query per category, which I don't think is efficient?

Thank you!

I'm needing some direction in how to start this...  I have a page with the 50 U.S. states listed.  I have a column in my mysql database that holds each state's name called:  source

There's a few thousands rows in my database, so the state names vary in the record count. 

How can I query the database and have a little count by each one of my 50 states that shows how many results exist in the db for that state?  I know how to query for total rows and show that number... but confused on how I would do it for individual line items.

Thanks for any suggestions. 

I am trying to display certain MySQL data based off of what link is clicked. For example.. I want to display all the entries from 2009 when a link for 2009 is clicked; maybe like..
<a href="http://website.com/page?yr=09">2009</a>
But I am having trouble doing so.. any help would be great!

P.S. The years are in my database with only the last two numbers.. 10, 09, 08, etc.

    $result = mysql_query("select setlist_id, month, day, year, name, venue, location, label1, set1, set2, set3, encore, notes, photos, recording, poster
        from $database_table order by setlist_id DESC, year, month, day",$db) 
        or die_now("Could not select setlists");

    while($row = mysql_fetch_array($result)) {
     $the_id = $row["setlist_id"];
        $the_month = $row["month"];
        $the_day = $row["day"];
        $the_year = $row["year"];
$the_name = $row["name"];
$the_venue = $row["venue"];
        $the_location = $row["location"];
        $the_label1 = $row["label1"];
$the_set1 = $row["set1"];
$the_set2 = $row["set2"];
$the_set3 = $row["set3"];
$the_encore = $row["encore"];
$the_notes = $row["notes"];
$the_photos = $row["photos"];
$the_recording = $row["recording"];
$the_poster = $row["poster"];

                echo("<div class='date' id='date' tabindex='0'>" . "$the_month" . "." . "$the_day" . "." . "$the_year" . " - ");
echo ($the_name != '') ? "" . "$the_name" . " - " : '';
echo("" . "$the_venue" . " - " . "$the_location" . "</div>");
                echo("<div class='set'>" . "$the_label1" . ":<br /></div><div class='list'>" . "$the_set1" . "<br /></div>");
                echo ($the_set2 != '') ? "<div class='set'>Set Two:<br /></div><div class='list'>" . "$the_set2" . "<br /></div>" : '';
echo ($the_set3 != '') ? "<div class='set'>Set Three:<br /></div><div class='list'>" . "$the_set3" . "<br /></div>" : '';
echo ($the_encore != '') ? "<div class='set'>Enco <br /></div><div class='list'>" . "$the_encore" . "<br /></sdiv>" : '';
                echo("<div class='line'></div>");
                echo nl2br("<div class='notes'>" . "$the_notes" . "</div>");
}

When I replace the $result line with...
    $result = mysql_query("SELECT * FROM `$database_table` WHERE `year` = '09' order by setlist_id DESC, year, month, day",$db) 
        or die_now("Could not select shows");... I get all the results from just 2009, which is what I want, but I want the results to depend on what link has been clicked. I hope I am making sense.

hello,
I have a search that matches users ISBN with two databases....code is below


$query_search_exact_match = mysql_query("SELECT 
   nvc_site.title, 
   nvc_site.id,
   nvc_site.description, 
   nvc_site.search_text, 
   nvc_site.image, 
   nvc_site.date, 
   nvc_site.price,
   nvc_site.location_city,
   nvc_site_ads_extra.name,
   nvc_site_ads_extra.value,
   nvc_site_ads_extra.classified_id
   
FROM   nvc_site,nvc_site_ads_extra
WHERE  name = 'ISBN%3A' AND live=1") or die(mysql_error());



then i take the ISBN that the user entered and match that with the isbn's in the DB


while ($fetch_extra = mysql_fetch_array($query_search_exact_match))
{

$value = ereg_replace( "[^0-9]", "", $fetch_extra['value'] );
$to_find_isbn = mysql_real_escape_string(ereg_replace( "[^0-9]", "",$_POST['szs']));
if($value == $to_find_isbn)
{
        echo "Match Found";
        } 
        else
        {
         echo "No Match Found";
         }
      //ELSE doesn't work here.....it displays both the if and else at the same time
}

i need it to display no match found if there was no match found.....I am soo lost right now!!

please help


thank you

Been using a function I found here a while back for listing categories and sub categories and it works perfect.
Code: [Select]
function listSubcats ($parent, $level=0){
global $abc;
$sql = "SELECT id, title FROM Cat WHERE parent = $parent";
$res = mysqli_query($abc, $sql);
while (list($id, $title) = $res->fetch_row()) {
$indent = str_repeat('-', $level);
echo "<OPTION value='$id'>$indent $title</OPTION>\n";
listSubcats ($id, $level+1);                       
// list its subcats
}
}
But this is the first time I need change the way the results are displayed.
And that leads me to realizing I am a bit confused on how list really works.

The mysql table has the typical ID, CatName, parent

I need to have each Cat with all subcats related to it listed in its own div.
But whatever I try I cannot get the placements of the opening and closing divs in the right spots.

So can anyone tell if its even possible to do it with this function.

I did work it out using multiple queries but then read on alot of forums that queries inside while statements is not good.

Just looking for he best (Correct) way of geting the results laid out properly.
 like this
Code: [Select]

<div class="one-third column">
 Cat1
</div>
<div class="one-third column">
 Cat 2
</div>
<div class="one-third column">
 Cat 3
 - Subcat 1
 - Subcat 2
 -- Sub Subcat 1
</div>
<div class="one-third column">
Cat 4
</div>

Hope that makes sense... Thanks for any guidance.

I would like to know what is the best method (the most intelligent and comfortable for users) to paginate a result table with PHP/MySQL?
Provided I have a result set of 100 pages.

Method 1:
<select>
<option>1</option>
....
</select>

Method 2:
Link on Page 1, 2, 3 ... 100

If there is any algorithm with PHP for automated these displays, please demonstrate it.

This topic has been moved to Application Design.

http://www.phpfreaks.com/forums/index.php?topic=347295.0

Hi guys,  Having problems sorting mysql results. I can easily order/sort table columns but would like to order the results by a variable.(Distance in Miles).
Havent much experience in PHP and really struggling with this. Basically a query echoing out car make and models and how far they are located from a postcode. Can get it to work no porblem, but not to order/sort by least distance.

Heres what i'm working with:
Code: [Select]
$res=mysql_query("SELECT * FROM cars");
while ($row = mysql_fetch_assoc($res)){

echo $row['Make'];
echo $row['Model'];

//variable will be determimed by user input form
$postcode="W12 3SR";

$start=$postcode;
$ends = array();

//finds value from database field
{
$ends[]=$row['Location'];
}

// Postcode entered by user via input form
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$start'");
while($row=mysql_fetch_array($result)){
$gridn[0]=$row['Grid_N'];
$gride[0]=$row['Grid_E'];
}

foreach($ends as $fin){
// Postcodes within mysql table
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$fin'");
while($row=mysql_fetch_array($result)){
$gridn[1]=$row['Grid_N'];
$gride[1]=$row['Grid_E'];
}

// TAKE GRID REFS FROM EACH OTHER TO WORK OUT DISTANCE.
$distance_n=$gridn[0]-$gridn[1];
$distance_e=$gride[0]-$gride[1];

// CALCULATE THE DISTANCE BETWEEN THE TWO POSTCODES AND DIVIDE BY 1.6 TO CONVERT KM TO MILES
$hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e))/1.609;

//VARIABLE FOR DISTANCE AND ROUNDED OF TO NEAREST WHOLE NUMBER.
$distance=''.round($hypot/1000,0).'';


  echo " $distance miles";
  echo "<br>";
}
}
?>
I wish i could just do something like this but isnt possible, is it ?
Code: [Select]
"Select * FROM cars ORDER BY $distance";
Thanks!