PHP - Problem With Displaying Query

Hello -

Suppose I had a MySQL result set that was something like this:
ITEM: COLOR: ball red ball blue book red book green book black
If I were to use the code:
Code: [Select]
do {
   echo $row_myQuery['item'] . " - " . echo $row_myQuery['color'] . "<br />";
} while ($row_myQuery= mysql_fetch_assoc($myQuery));

Then I end up with this:

Code: [Select]
ball - red
ball - blue
book - red
book - green
book - black
MY QUESTION:

What I'd like to know is, how could I re-write that php code so that I could get an output like this?:

Code: [Select]
ball - red, blue
book - red, green, black
Thanks!

What is the best way to display the next record on a web page, from a list of records, without executing the query over and over again each time you display the next record?

For example, imagine my user has a list of messages displayed on a web page, and the user filters that list to display all messages with the title "Test", and let's say that returns 12 records.

He then clicks on record 1 to read that message. Once he is done reading it, he then wants to read msg 2, and then msg 3 etc.

He could hit the Back button in his browser and click on the next record from the list, but it would be more useful to just click on a link that says "Next" which displays the next message in the filtered list of messages (without having to go back to the list).

A lot of web sites do this sort of thing, but what is the best way to do it? Do you have to run the original query again each time a new message is loaded, so you know which message is next, or can you somehow store the query to save the server from having to run the same query over and over again?

If a user is viewing a list of pictures for example, they could be flicking through the pictures quite quickly, and I would not like to have the server running the same query over and over again every few seconds when it doesn't need to.

Advice? (Thanks.) 

Hey all,

First off, if this is in the wrong section I apologize. I wasn't sure if it should be here or the mySQL section.

What's going on is, I'm in the process of learning the Prepared Statement way of doing things and am changing / updating my code to reflect the changes. Everything was going fine until I attempted to do what I could do using old MySQL methods and that is display the queried results on the same page.


I can place a query and display the results as they should be displayed if I only use one block of code. However, if I try to do any additional queries on the same page, they get killed and do not display anything even though I know the query is fine because I can test the exact same syntax below one a different page and it works.

Here's a code snippet for an example:
Code: [Select]

Code:
<table>
<tr>
<td>
// The below code will display a selection box containing various strings such as "hello world", "great to be here", "Wowserz", "this is mind blowing" etc. that are stored in the database.

<?php

   echo "<select = \"SpecialConditions\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
   
       echo "<option value=\"$specialId\">$specialcondition</option>";
   }
}
   echo "</select>";
?>
</td>
<td>
// If I place another fetch query below the above fetch() query, this one will not show up. This one is supposed to display values 1 - 20 that have been stored in the DB.

<?php
echo "<select = \"NumberSets\">";
   if($stmt->num_rows == NULL){
      echo "No results found.";
   }else{
      while($stmt->fetch()){
      
          echo "<option value=\"".$numbers."\">".$numbers."</option>";
      }
   }
echo "</select>";

?>

</td>
</tr>
</table>


What am I doing wrong with this? When I use regular SQL queries I can display multiple results on the same page.

The results are being pulled from two separate joined tables but I don't think that's the issue.

Ok so I am pretty good with mysql and performing queries to get the data that I need. However, I have a question for any of you gurus out there that may be able to help me with an issue that I always run into with certain types of queries. Say I have 2 tables like this:

Galleries
id name 1 My Photos
Gallery Photos
id gallery_id photo 1 1 photo1.jpg 2 1 photo2.jpg 3 1 photo3.jpg
Now usually when I pull this info from the database I have to do 2 separate queries in order to get the data and then link them so I would do something like.


<?php
//make the gallery query
$query = "SELECT * FROM galleries";
$results = mysql_query($query);

//setup an array for the galleries data
$gallery_data = array();

//loop through the galleries
for($i=0;$i<mysql_num_rows($results);$i++){
//add the gallery info to the gallery data array
$gallery_data[$i] = mysql_fetch_array($results);

//make the photos query with the gallery id
$photo_query = "SELECT * FROM gallery_photos WHERE gallery_id='".mysql_real_escape_string($gallery_data[$i]['id'])."'";
$photo_results = mysql_query($photo_query);

//setup the photos array
$photo_data = array();

//put the photos in the array
for($n=0;$n<mysql_num_rows($photo_results);$n++){
$photo_data[$n] = mysql_fetch_array($photo_results);
}

//add the photos to the gallery array
$gallery_data[$i]['photos'] = $photo_data;
}

//now to display it is like this
if(is_array($gallery_data)){
foreach($gallery_data as $gallery){
echo $gallery['name'];

//show the photos
if(is_array($gallery['photos'])){
foreach($gallery['photos'] as $photo){
echo $photo['photo'];
}
}
}
}
?>


So my question is there a way to get all of this data at one time. I know how to do multiple queries in one and to do joins but they can only return one row as far as I know of. The only other way that I know how to do this is by ordering them by name and selecting them directly from the photos table and then just getting the gallery name like this:


SELECT *,(SELECT name FROM galleries WHERE id=gallery_photos.id) AS gallery_name FROM gallery_photos ORDER BY gallery_id


Then I could just do one loop and see if the name has changed and if so to output the new name. But I would like to know if there is a way to get a second set of results as an array from a query so I could just select the galleries and photos all in one query. Any help is appreciated.

Below is a page which is supposed to output the name, blog contribution and picture of contributing members of a website.
<div id="blog_content" class="" style="height:90%; width:97%; border:5px solid #c0c0c0; background-color: #FFFFFF;"> <!--opens blog content--> 



                             
<?php


//address error handling

ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);



//include the config file

require_once("config.php");



//Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table.


$sql = "SELECT


    blogs.title,blogs.entry,members.firstname,images.image
FROM
    blogs
LEFT JOIN
    members
ON
    blogs.member_id = members.member_id
LEFT JOIN
    images
ON
    blogs.member_id = images.member_id

ORDER BY
     blogs.title ASC

"; 

$query = mysql_query($sql);

                 if($query !== false && mysql_num_rows($query) > 0)

                 {

         while(($row = mysql_fetch_assoc($query)) !== false)

         {

 echo

                
                              '<div id="blog_content1" style="float:left; position:relative;bottom:18px;left:13px; background-color: #FFFFFF; height:16.7%; width:100%; border:0px none none;"                              
                              <!--opens blog_content1  same as main center top 1 and 2 from index page everything scaled down by a factor of 3, heightwise--> 
        
 
                                   <div class="red_bar" style="height:3%; width:100%; border:1px solid #959595;"> <!--a--> 
 
                                       <div class="shade1" style="height:5px; width:100%; border:0px none none;"> </div>                                      
                                       <div class="shade2" style="height:5px; width:100%; border:0px none none"> </div> 
                                       <div class="shade3" style="height:5px%; width:100%; border:0px none none"> </div>

 
                                   </div> <!-- closes red bar--> 
 
 
 
                                   <div class="content" style="height:28.3%; width:100%; border:0px none none;"> <!----> 
 
                                       
                                           <div class="slideshow" id="keylin" style="float:left; width:20%;  border:0px none none;"> <!--a-->
 
                                                  
                                                  
 
                                                 <div><img header("Content-type: image/jpeg"); name="" alt="" id="" height="105" width="105" src="$row[image]" /></div>                              
 
 
                                       
                                           </div> <!-- closes pic--> 
 
 
 
                                           <div class="content_text"  style="float:right;  position:relative;top:7px;left:0px; max-height:150px; width:78.5%; border-width:4.5px; border-bottom-style:solid; border-right-style:solid; border-color:#c0c0c0; "> <!--a-->'; 
 
                                           

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

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

         
                                             
                                                                                           
                                           echo                                         
                                           '</div> <!-- closes content text--> 
 
  
                                   </div> <!-- closes content--> 
 
                                   
                                                                                                                                                                                                                            
                            </div> <!-- closes blog_content1-->';



                                                 }

                 }

                 else if($query == false)

                 {

               

 echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>";

         echo "<p>The query being run was \"".$sql."\"</p>";

                 }

                 else if($query !== false && mysql_num_rows($query) == 0)

                 {

         echo "<p>The query returned 0 results.</p>";

                 }


                                                 mysql_close(); //Close the database connection.





                                                 ?> 

                      
                        </div> <!-- closes blog content-->


The select query is designed to retrieve all the blog contributions(represented by the fields blogs.title and blogs.entry) from the database, alongside the contributing member (member.firstname) and the member's picture(images.image), using the member_id column to join the 3 tables involved, and outputs them on the webpage.   The title, entry and firstname values are successfully displayed on the resulting page.  However, I can't seem to figure out how to get the picture to be displayed.  Note that the picture was successfully stored in the database and I was able to view it on a separate page using a simple select query.  It is now just a question of how to get it to display on this particularly crowded page.  Anyone knows how I can output the picture in the img tag?  I tried placing the header("Content-type: image/jpeg"); statement at the top of the php segment, then just right below the select query and finally just right above the img tag, but in every case, I just got a big white blank page starring at me.  How and where should I place the header statement? And what else am I to do to get this picture displayed?  Any help is appreciated.

On my web page, there is a variable called $submission. I would like to display exactly 11 rows from the query below: the row where $submission equals $row["title"], 5 rows above it, and 5 rows below it. All ranked by points descending.

How can I do this?

Code: [Select]
$sqlStr = "SELECT title, points, submissionid
             FROM submission
         ORDER BY points DESC";


$result = mysql_query($sqlStr);

$arr = array();

$count=1;

echo "<table class=\"samplesrec\">";

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


    echo '<tr >';

    echo '<td>'.$count++.'.</td>';

    echo '<td class="sitename1">'.$row["title"].'</td>';

    echo '<td class="sitename2"><div class="pointlink2">'.number_format($row["points"]).'</div></td>';
    echo '</tr>';

}

echo "</table>";

hey, im trying to show data in a text box thats being retrived from a database, but if there is a ' in it, it will only show the text up to that point. (ie. if it has "You're" it will only show "You")
here is the code im using


echo "<input type='text' value='$picComment'>";

i cannot figure out why my first result is not displaying properly it successfully shows all results but me first
myurl.com/other.php?id=2 now showing any thing kindly help to solved this problem! 

it seems like problem is in variable $http$ids but dunno how to combine them to get result $Ids always b numeric number

<?php
ini_set ("display_errors", "1"); 
error_reporting(E_ALL);

$http = "myurl.com/other.php?id=" ;

$conn=odbc_connect('greeting','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM mytable";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table border= 1><tr>";
echo "<th>ID</th>";
echo "<th>Like</th>";
echo "<th>title</th></tr>";
while (odbc_fetch_row($rs))
  {
  $ids=odbc_result($rs,"ID");
  $title=odbc_result($rs,"title");
  echo "<tr><td>$ids</td>";
  echo "<td><fb:like href=\"$http$ids\" layout=\"button_count\"  show_faces=\"false\" width=\"100\" font=\"tahoma\" colorscheme=\"dark\"></fb:like> </td>";
  echo "<td>$title</td></tr>";
  }
echo "</table>";
odbc_close($conn);
?>

Hi everyone

I just getting back to PHP after a break but have forgot how to build a HTML table row. Here is the part of the code that builds up rows for a html table...



while($rows = mysql_fetch_array($qry)){
        
           $table .= "
           <tr>

<td><input type=\"checkbox\" name=\"C1\" value=\"ON\"></td>

<td width=\"87\">$rows['ref']</td>

<td width=\"178\">$rows['transactionReferenceNumber']</td>

<td>$rows['totalAmountReceived']</td>

<td>$rows['myItemQuantity']</td>

<td>$rows['flag_1']</td>

<td>$rows['flag2']</td>

</tr>

           ";

}



This causes an error as you might expect. Please tell me the best way to place the html code into $table

Hey, I have written a script for a very simple PHP wall and comment system. This works fine but the problem I have is displaying the comments. It seems to display the comments associated with the post as well as the comments on the posts above it. I have checked the database and the  post ID's are correct.

Here is my code:

Code: [Select]
<?php
$wallDisplay = '';
$commentDisplay = '';

$wallDisplaySql = mysql_query("SELECT * FROM wall WHERE to_id='$id' ORDER BY datetime DESC") or die (mysql_error());

while($row = mysql_fetch_array($wallDisplaySql)){
$wallPostId = $row["id"];
$to_id = $row["to_id"];
$from_id = $row["from_id"];
$message = $row["message"];
$dateTime = $row["datetime"];


$getFromData = mysql_query("SELECT username FROM members WHERE id='$from_id'") or die (mysql_error());

while($row2 = mysql_fetch_array($getFromData)){
$wallUsername = $row2['username'];
}

$displayComments = mysql_query("SELECT * FROM wallComments WHERE wallPostId='$wallPostId' ORDER BY datetime DESC");


while($row3 = mysql_fetch_array($displayComments)){
$wallComment = $row3['comment'];
$commentFrom = $row3['from_id'];
$commentDate = $row3['datetime'];


$getUsername = mysql_query("SELECT username FROM members WHERE id='$commentFrom'");

while($row4 = mysql_fetch_array($getUsername)){
$commentUsername = $row4['username'];
}

$cheersCheck_pic = "members/$commentFrom/pic1.jpg";
$cheersDefault_pic = "members/0/defaultMemberPic.jpg";
if (file_exists($cheersCheck_pic)) {
    $cheers_pic = "<img src=\"$cheersCheck_pic?$cacheBuster\" width=\"40px\" />"; 
} else {
$cheers_pic = "<img src=\"$cheersDefault_pic\" width=\"40px\" />"; 
}

$commentDisplay .= '<table width="500px" align="right" cellpadding="4" bgcolor="#FFF">
        <tr>
          <td width="10%" bgcolor="#FFFFFF"><a href="member_profile.php?id=' . $commentFrom . '">' . $cheers_pic . '</a><br />
          </td>
          <td width="90%" bgcolor="#DBE4FD"><a href="member_profile.php?id=' . $commentFrom . '"><span class="blackText">' . $commentUsername . '</span></a> &bull; <span class="blackTetx">' . $commentDate . '<br /><font size="1"></font></span><br />
          <span class="blackText">' . $wallComment . '</span></td>
        </tr>
      </table>';
}

$cheersCheck_pic = "members/$from_id/pic1.jpg";
$cheersDefault_pic = "members/0/defaultMemberPic.jpg";
if (file_exists($cheersCheck_pic)) {
    $cheers_pic = "<img src=\"$cheersCheck_pic?$cacheBuster\" width=\"40px\" />"; 
} else {
$cheers_pic = "<img src=\"$cheersDefault_pic\" width=\"40px\" />"; 
}

$wallDisplay .= '<table width="100%" align="center" cellpadding="4" bgcolor="#FFF">
        <tr>
          <td width="7%" bgcolor="#FFFFFF"><a href="member_profile.php?id=' . $from_id . '">' . $cheers_pic . '</a><br />
          </td>
          <td width="93%" bgcolor="#DBE4FD"><a href="member_profile.php?id=' . $from_id . '"><span class="blackText">' . $wallUsername . '</span></a> &bull; <span class="blackTetx">' . $dateTime . '<br /><font size="1"></font></span><br />
          <span class="blackText">' . $message . '</span></td>
        </tr>
      </table>
  <div id="commentList">' . $commentDisplay . '</div>
  <div id="comment" align="right">
<form id="comment" name="comment" method="post" action="member_profile.php?id=' .$id. '">
<textarea name="comment" id="comment" rows="1" cols="35"></textarea>
<input type="hidden" name="wallPostId" id="wallPostId" value="'. $wallPostId .'" />
<input type="hidden" name="commentFrom" id="commentFrom" value="'. $_SESSION['id'] .'" />
<input type="submit" name="submitComment" id="submitComment" />
</form>
</div><br />
';
}
?>

I have been looking at it for ages but can think why this is happening.

Thanks in advance for any help

the script succesfuly insert image to the database bt, i cant be able to display it on my pages, any help i will appreciate Attached Files  saveimage.php   1.15KB   2 downloads  images_tbl.php   192bytes   3 downloads

This topic has been moved to CSS Help.

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

I am querying my database to show the visit statistics for a particular week and it shows the number of visits for the countries, but does not display the country name. I have proved that the MySQL works by going into phpMyAdmin and pasting the query into SQL query tab, replacing the POST with 1, for week 1.   I can't see why it is not displying the country.   Here is the code:

<?php
include('connect_visits.php');
doDB7();

$WVisit_data="SELECT WeekNo15.WNo, WeekNo15.WCom, Countries.Country, Countries.CID, ctryvisits15.CVisits
        FROM ctryvisits15 
        LEFT JOIN Countries
            ON ctryvisits15.country = Countries.CID
        LEFT JOIN WeekNo15
            ON ctryvisits15.WNo = WeekNo15.WNo
        WHERE ctryvisits15.WNo = '{$_POST['WeekNo']}'
        ORDER BY ctryvisits15.CVisits DESC";

$WVisit_data_res = mysqli_query($mysqli, $WVisit_data) or die(mysqli_error($mysqli));
$display_block ="
<table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" >
<tr>
<th>Country</th>
<th>Visits</>
</tr>";
while ($WV_info = mysqli_fetch_array($WVisit_data_res)){
    $Ctry = $WV_info['country'];
    $Visits = $WV_info['CVisits'];
//add to display
$display_block .="
<tr>
<td width=\"10%\" valign=\"top\">".$Ctry."<br/></td>
<td width=\"5%\" valign=\"top\">".$Visits."<br/></td>
";
}
mysqli_free_result($WVisit_data_res);
mysqli_close($mysqli);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Design by Free CSS Templates
http://www.freecsstemplates.org
Released for free under a Creative Commons Attribution 2.5 License

Name       : Yosemite  
Description: A two-column, fixed-width design with dark color scheme.
Version    : 1.0
Released   : 20091106

-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>1066 Cards 4U - Stats for country</title>
<link href="style.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
<div id="wrapper">
<div id="menu">
		<ul>
			<li class="current_page_item"><a href="index.php">Home</a></li>
			<li><a href="Links.html">Links</a></li>
			<li><a href="Verse_Menu.html">Verses</a></li>
			<li><a href="Techniques.html">Techniques</a></li>
			<li><a href="blog.php">Blog</a></li>
			<li><a href="Gallery.html">Gallery</a></li>
			<li><a href="contact.html">Contact</a></li>
			<li><a href="AboutUs.html">About Us</a></li>
			<li><a href="stats1.html">Stats</a></li>
		</ul>
</div><!-- end #menu -->
<div id="header">
		<div id="logo">
			<h1><a href="http://www.1066cards4u.co.uk">1066 Cards 4U</a></h1>
</div><!-- end #wrapper -->
</div><!-- end #header -->
<div id="page">
<div id="page-bgtop">
<div id="page-bgbtm">
<div id="content">
<h3>Statistics for Week Commencing <? echo $WkCom; ?> in 2015</h3>

<div id="table">
<?php echo $display_block; ?></div>
</div><!-- end #content -->
</body>
</html>

Can you help please?
Edited by rocky48, 07 January 2015 - 07:33 AM.

I'm using mcrypt to encrypt a field and decrypt it.  That works OK.  But when I insert the encrypted field into a mySQL table, select it, decrypt it and try to display it, I get garbage.

What am I missing?  Thanks for any suggestions.

Here's the code:


echo "The encrypt and decrypt example...<br/>";

// Designate string to be encrypted
$string = "Ed O'Reilly";

// Encryption/decryption key
$key = "arG4thaker2";

// Encryption Algorithm
$cipher_alg = MCRYPT_RIJNDAEL_128;

// Create the initialization vector for added security.
$iv = mcrypt_create_iv(mcrypt_get_iv_size($cipher_alg, 
MCRYPT_MODE_ECB), MCRYPT_RAND);

// Output original string
print "Original string: $string <p>";

// Encrypt $string
$encrypted_string = mcrypt_encrypt($cipher_alg, $key, 
$string, MCRYPT_MODE_CBC, $iv);

// Convert to hexadecimal and output to browser
print "The encrypted string: ".bin2hex($encrypted_string)."<p>";

$decrypted_string = mcrypt_decrypt($cipher_alg, $key, 
$encrypted_string, MCRYPT_MODE_CBC, $iv);

print "The decrypted string: ".rtrim($decrypted_string)."";

echo "<br/>testing db storing & retrieval for the encryption/decryption...<br/>";

# make connection (Game server connections are variable and are done later when needed)
if (!$cxnAdmin = mysqli_connect($hostAdmin, $userAdmin, $passwordAdmin, $dbnameAdmin))
{
die("Could not connect to MySQL server Admin (".mysqli_connect_error().")");
}

$sql="INSERT INTO testdb.nametable (
name,
email
)
VALUES (
'$encrypted_string',
'test@test.com'
)
;
";

$result = mysqli_query($cxnAdmin,$sql);
if($result == false)
{
echo "<h4>Error on nametable Insert: ".mysqli_error($cxnAdmin)."</h4>";
}

$sql="SELECT name
FROM testdb.nametable
WHERE email='test@test.com'";

if (!$result = mysqli_query($cxnAdmin,$sql))
{
echo "<h4>Error on nametable Select: ".mysqli_error($cxnAdmin)."</h4>";
}

$row = mysqli_fetch_assoc($result);
extract($row);

// Convert to hexadecimal and output to browser
print "The encrypted string from db: ".bin2hex($name)."<p>";

$decrypted_name = mcrypt_decrypt($cipher_alg, $key, 
$name, MCRYPT_MODE_CBC, $iv);

print "The decrypted string from db: ".rtrim($decrypted_name)."";


and here's the output:

The encrypt and decrypt example...
Original string: Ed O'Reilly

The encrypted string: 01bc70f5dc5a7ca204cb45bd2eeb0214

The decrypted string:Ed O'Reilly
testing db storing & retrieval for the encryption/decryption...
The encrypted string from db: 5938f7fc81961d2efc6db64607baf465

The decrypted string from db: <garbage characters I can't display here>