PHP - Result Query Multiple Tables Next To Eachother

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.

Im doing a search system and Im having some problems. I need to search in two tables (news and pages), I already had sucess doing my search system for just one table, but for two tables its not easy to do. I already use a select statment with two tables using UNION because I want to show number of search results, that is number of returned rows of my first sql statment. But now I need to do a select statment that allows me to acess all fields of my news table and all fields of my pages table. I need to acess in my news table this fields: id, title, content, link, date, nViews I need to acess in my pages table this fields: id, title, content, link Im trying to do this also with UNION, but in this case Im not having any row returning. Do you see what I have wrong in my code?   <?php
//first I get my $search keyword
$search = $url[1];

$pdo = connecting();
//then I want to show number of returned rows for keyword searched
$readALL = $pdo->prepare("SELECT title,content FROM news WHERE title LIKE ? OR content LIKE ?
UNION SELECT title,content FROM pages WHERE title LIKE ? OR content like ?");
$readALL->bindValue(1,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(2,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(3,"%$search%", PDO::PARAM_STR);
$readALL->bindValue(4,"%$search%", PDO::PARAM_STR);
$readALL->execute();
//I show number of returned rows
echo '<p>Your search keyword returned <strong>'.$readALL->rowCount().'</strong> results!</p>';
//If dont return any rows I show a error message
if($readALL->rowCount() <=0){
echo 'Sorry but we didnt found any result for your keyword search.';
}
else{
//If return rows I want to show, if it is a page result I want to show title and link that I have in my page table
//if it is a news result I want to show title and link that I have in my news table and also date of news
echo '<ul class="searchlist">';
$readALL2 = $pdo->prepare("SELECT * FROM news WHERE status = ? AND title LIKE ? OR content LIKE ? LIMIT 0,4
UNION SELECT * FROM pages where title LIKE ? OR content LIKE ? LIMIT 0,4");
$readALL2->bindValue(1, '1');
$readALL2->bindValue(2, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(3, "%$search%", PDO::PARAM_STR);
$readALL2->bindValue(4, "%$search%", PDO::PARAM_STR);
$readALL2->execute();

while ($result = $readALL2->fetch(PDO::FETCH_ASSOC)){
echo '<li>';
echo '<img src="'.BASE.'/uploads/news/'.$result['thumb'].'"/>';
echo '<a href="'.BASE.'/news/'.$result['id_news'].'">'.$result['title'].'</a>';
//if it is a news result I also want to show date on my list
//echo '<span id="date">'.$result['date'].'</span>';
echo '</li>';
}
echo ' </ul>';
//but how can I do my select statement to have access to my news table fields and my page table fields??
}
?>

Hello all.   I am using a premade login script which has some very powerful function built in, the one I am looking at using is as follows:

  /** 
    * query - Performs the given query on the database and 
    * returns the result, which may be false, true or a 
    * resource identifier. 
    */ 
   function query($query){ 
      return mysqli_query( $this->connection, $query); 
   } 

The issue is, displaying the results.  I call is via the following code:

$database->query('SELECT LastName, Firstname FROM Patrons WHERE LastName = "Delude"');

But I have no idea how to display it.  I have attempted to echo it out, but I get the following error in the logs:   PHP Catchable fatal error:  Object of class mysqli_result could not be converted to string    

hello
I'm using this code:
Code: [Select]
$query="SELECT * FROM `second_content` WHERE CHANGED =0 limit 0,1";to query first row data when I want see it and echo it I recived :
Code: [Select]
Resource id #2can anyone help me ?


thank you

Hi everyone. I have this code that suppose to divide the query result 3 columns in a row. Like this :
Shoes   Hats   Jeans
Shorts  Shirts  Belts
But for some reason this one doesn't work. It gave me everything in a single row. Like this: Shoes Hats jeans Shorts Shirts Belts. I noticed that before I put this two 2 lines of code, it's working fine. It didn't give me any error messages or anythings. Can someone point me out what did I did wrong?

This is two lines I put in:
Code: [Select]
$vuong_query = tep_db_query("select categories_id from " . TABLE_GENDER_CATEGORIES . " where parent_id = 1");
 while ($product_check = tep_db_fetch_array($vuong_query)) {
This is the whole piece of code:
    Code: [Select]
  <tr>
    <td><table width = "100%" border="1" bordercolor="maroon">
      <tr>
<?php
$vuong_query = tep_db_query("select categories_id from " . TABLE_GENDER_CATEGORIES . " where parent_id = 1");
 while ($product_check = tep_db_fetch_array($vuong_query)) {

$categories_querys = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . $product_check['categories_id'] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");
    

    $number_of_categories = tep_db_num_rows($categories_querys);

    $rows = 0;
    while ($categories = tep_db_fetch_array($categories_querys)) {
      $rows++;
      $cPath_new = tep_get_path($categories['categories_id']);
      $width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%';
      echo '         <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n";
      if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) {
echo '       </tr>' . "\n";
echo '       <tr>' . "\n";
      }
    }
}
    ?>
      </tr>
            </table>
          </td>
        </tr>
Thanks anyone for helping me out.
Vanvoquan

Hi all !,   I am stuck on the following piece of code which does not give an error nor does it give a result. ( i.e. it gives 0 num_rows which should be > 1).    If, however, I execute the query in phpmyadmin by simply substituting the values of $pp,$ll and $room_no in the query it gives the correct result.      Please can someone tell me what I may be doing wrong here.  Thanks !

	$fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection');

				$pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')";	  
				$ll = 'ms.diff <= 7';
				$room_no = 4;

				$query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, 
						ms.level, ms.diff, ms.score,  
						r.ID_Status 
						FROM register as r
						JOIN member_detail as md ON r.ID = md.Member_reg_id  
						JOIN memstatus as ms On r.ID = ms.ID  
						WHERE r.CENTERCODE = ? AND r.ID_Status ='A' AND ? AND ?
						ORDER by level, diff, score DESC";
		
				$stmt=$fcon->prepare($query);
				$stmt->bind_param('iss',$room_no,$pp,$ll);
				if(!$stmt->execute()) die('Failed to execute the query'.$fcon->error);
				else
				{
					echo "Executed";
					$stmt->bind_result($Member_reg_id,$fname,$lname,$email,$cell,$level,$diff                                               ,$score,$ID_Status);
					$numrows = $stmt->num_rows;
					$stmt->store_result();
//					echo $numrows;
					while($stmt->fetch())
					{
						echo "<br>".$fname.' '.$lname;
						echo "<br>".$level;
						echo "<br>".$diff;
						echo "<br>".$score;
						echo "<br>".$cell;
						echo "<br>".$email;
					}
				}

Edited by ajoo, 03 January 2015 - 08:00 AM.

Hello,
So I have a content management site set up, but I'm stuck on one part. I have a query. Code: [Select]
Select `email` FROM author WHERE author.id IN(SELECT authorid FROM job WHERE id = '$_POST[id]')
I'm a beginner in PHP and SQL. Basically I need the query to run, it will come out with one result and I need that to be where the email is sent to.

This is the existing code I have for sending out the email $subject = " Job Application Confirmation";

$message = "Hello $_POST[name] , \r\rYou, or someone using your email address, has applied for the job: $text Using the Resource Locator at Prahan.com. Keep this as future reference. \r\r Full Name: $_POST[name] \r Location: $_POST[location] \r Email: $_POST[email] \r Additional Information: $_POST[info] \r Job ID: $id  \r Job Name: $text  \r \r Expect a response shortly. \r\r Regards, Prahan.com Team";

$headers = 'From: Prahan.com Team <noreply@prahan.com>';

    'Reply-To: noreply@prahan.com' . "\r\n" .

    'X-Mailer: PHP/' . phpversion();

$to1      = 'pratik@prahan.com';

mail($to1, $result, $message, $headers);


Thanks in Advance!

Hi there I am new to this forum and not much of a php expert. I hope you can assist me. My problem is as follows: I have a column consisting of several values (in this case: sceience, art, humanistics and music) I would like to use the following mysqli query in order to count the appearances of each value in this column:     $query = "SELECT SUM (IF(department = 'science', 1, 0)) AS science, SUM(IF(department = 'art', 1, 0))-> AS art FROM new2"   I have tried lots of ways to loop the results but couldn't get the it working. Can anyone suggest any idea?   Thanks   Hanan  

This topic has been moved to MySQL Help.

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

I am trying to export multiple csv files to download from 2 separate tables in my database.

Some background:
I have a web app that links to a phpmyadmin database. There are 2 tables in the database (entering and exiting). These tables hold the phone inventory information of employees currently entering or exiting the organization.

The fields in my tables a

location, firstname, lastname, username, extension, mac, type

What I am trying to do is export the data in these 2 tables to CSV (which I have working now) but I need to have multiple CSVs for each.

For example, for my exiting table, I need to have one CSV export all the fields in the table and I also need another CSV to export just the location and username field. I have a simple html form with a submit button which is currently working now:

<form action="exportexiting.php" method="POST" style="width: 456px; height: 157px">
   <div>
   <fieldset>
   <legend style="width: 102px; height: 25px"><strong>Entering CSV:</strong></legend>
   <input name="Export" type="submit" id="Export" value="Export">
   </fieldset><br/>
   </div>
</form>


This links to my exportexiting.php file:

<?php
$host = 'localhost';
$user = 'root';
$pass = 'xxxx';
$db = 'PhoneInventory';
$table = 'exiting';


 
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
 
require 'exportcsv.inc.php';

 
exportMysqlToCsv($table);
 
 
 
?>

Then to exportcsv.inc.php:

<?php
 
function exportMysqlToCsv($table,$filename = 'export.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '';
    $csv_escaped = "\\";
    $sql_query = "select * from $table";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
 
 
    $schema_insert = '';
 
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
 
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
 
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
 
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed .
               str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
 
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
 
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
 
}
 
?>



Again, this is working perfectly for only exporting all the data from the exiting table into one CSV file. My question is, how can I make my one submit button export the 2 CSV files that I need?


Thanks for the help...

I have a form where a user can duplicate part of a form like below:


I've named the fields with the HTML arrays (ie. "fieldName[]") and came up with this:

//--> CONTROLLERS
$system_controllers_qty = $_POST['system_controllers_qty'];
$system_controllers_make = $_POST['system_controllers_make'];
$system_controllers_model = $_POST['system_controllers_model'];
$system_controllers_serial_no         = $_POST['system_controllers_serial_no'];
for ($i = 0; $i < count($system_controllers_qty); $i++)
{
echo "qty " . clean($system_controllers_qty[$i]) . "<br/>";
echo "make " . clean($system_controllers_make[$i]) . "<br/>";
echo "model " . clean($system_controllers_model[$i]) . "<br/>";
echo "serial_no " . clean($system_controllers_serial_no[$i]) . "<br/>";
}

//--> CPUS
$system_cpus_qty = $_POST['system_cpus_qty'];
$system_cpus_model = $_POST['system_cpus_model'];
$system_cpus_serial_no = $_POST['system_cpus_serial_no'];
$system_cpus_speed = $_POST['system_cpus_speed'];
for ($i = 0; $i < count($system_cpus_qty); $i++)
{
echo "qty " . clean($system_cpus_qty[$i]) . "<br/>";
echo "model " . clean($system_cpus_model[$i]) . "<br/>";
echo "serial_no " . clean($system_cpus_serial_no[$i]) . "<br/>";
echo "speed " . clean($system_cpus_speed[$i]) . "<br/>";
}

//--> DISKS
$system_disks_qty = $_POST['system_disks_qty'];
$system_disks_make = $_POST['system_disks_make'];
$system_disks_model_no = $_POST['system_disks_model_no'];
$system_disks_size = $_POST['system_disks_size'];
$system_disks_serial_no = $_POST['system_disks_serial_no'];
for ($i = 0; $i < count($system_disks_qty); $i++)
{
echo "qty " . clean($system_disks_qty[$i]) . "<br/>";
echo "make " . clean($system_disks_make[$i]) . "<br/>";
echo "model_no " . clean($system_disks_model_no[$i]) . "<br/>";
echo "size " . clean($system_disks_size[$i]) . "<br/>";
echo "serial_no " . clean($system_disks_serial_no[$i]) . "<br/>";
}

//--> MEMORY
$system_memory_qty = $_POST['system_memory_qty'];
$system_memory_serial_no = $_POST['system_memory_serial_no'];
$system_memory_manf = $_POST['system_memory_manf'];
$system_memory_part_no = $_POST['system_memory_part_no'];
$system_memory_size = $_POST['system_memory_size'];
for ($i = 0; $i < count($system_memory_qty); $i++)
{
echo "qty " . clean($system_memory_qty[$i]) . "<br/>";
echo "serial " . clean($system_memory_serial_no[$i]) . "<br/>";
echo "manf " . clean($system_memory_manf[$i]) . "<br/>";
echo "part_no " . clean($system_memory_part_no[$i]) . "<br/>";
echo "size " . clean($system_memory_size[$i]) . "<br/>";
}


This just outputs all the fields I post at the moment. Say I have 3 different disk types, it's going to loop 3 times. Is it possible to make this all into 1 giant query or am I better off doing each query separately? Each section is a different table.

I have a search where I want to be able to search a string of words. The search is going to be looking in 2 different table joined by a left outer join. The tables are "quotes" and "categories".

$sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')";
   
The above mysql statement works and returns values..BUT if say, I search "john" and "funny", and a quote is posted by the user "john", but has a category of "funny" it will output the same quote twice. I was wondering if there was a way to see if a quote has either 1 term or both terms, if so display that quote but only display it once. Below is what the query is outputting.


[100] => Array
        (
            [id] => 100
            [username] => John
            [quote] => new test quote blah blah
            [by] => John
            [voteup] => 0
            [votedown] => 0
            [servtime] => 2010-12-02 @ 16:27:03
            [label] => Array
                (
                    [0] => Historic
                    [1] => Serious
                    [2] => Funny
                )

        )


Here is the code in full.


////
$sword = explode(" ",$search);
foreach($sword as $sterm){
$srch="%".$sterm."%";
echo"$srch<br />";
$sql="SELECT q.id, q.username, q.quote, q.by, q.voteup, q.votedown, q.servtime, c.quote_id, c.label FROM quotes q LEFT OUTER JOIN categories c ON q.id = c.quote_id WHERE ( q.username LIKE '$srch' OR q.quote LIKE '$srch' OR q.`by` LIKE '$srch' OR c.label LIKE '$srch')";
$result=mysql_query($sql);
while($row=mysql_fetch_object($result)){
$quote[$row->id]['id'] = $row->id;
$quote[$row->id]['username'] = $row->username;
$quote[$row->id]['quote'] = $row->quote;
$quote[$row->id]['by'] = $row->by;
$quote[$row->id]['voteup'] = $row->voteup;
$quote[$row->id]['votedown'] = $row->votedown;
$quote[$row->id]['servtime'] = $row->servtime;
$quote[$row->id]['label'][] = $row->label;
}
echo"<pre>";
print_r($quote);
echo"</pre>";


I don't think this is the fastest way of doing this, as it loops for each item in the db, per each keyword that is search.

Any help would be great!!
-BaSk

This topic has been moved to MySQL Help.

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

Hi,

I have attached sql script for table structure and sample data.

When I run following sql query ,I get result set.

Code: [Select]
select * from tablename order by col10 desc
I want to create array using result set.

Following is the require structure of array.

Code: [Select]
<?php

$resultSet[]=array
    (
        ('0') =>array (
              ('col10value1') => array
                    (
                        ('0') => array
                            (
                                ('col1') => 'col1',
                                ('col2') => 'col2',
                                ('col3') => 'col3',
                                ('col4') => 'col4',
                                ('col5') => 'col5',
                                ('col6') => 'col6',
                                ('col7') => 'col7',
                                ('col8') => 'col8',
                            ),
                        ('1') => array
                            (
                                ('col1') => 'col1',
                                ('col2') => 'col2',
                                ('col3') => 'col3',
                                ('col4') => 'col4',
                                ('col5') => 'col5',
                                ('col6') => 'col6',
                                ('col7') => 'col7',
                                ('col8') => 'col8',
                            ),
                    ),
            ),
 

        ('1') => array (
          ('col10value2') => array
                (
                    ('0') => array
                        (
                            ('col1') => 'col1',
                            ('col2') => 'col2',
                            ('col3') => 'col3',
                            ('col4') => 'col4',
                            ('col5') => 'col5',
                            ('col6') => 'col6',
                            ('col7') => 'col7',
                            ('col8') => 'col8',
                        ),
                ),
        ),
    );   
?>

Hi all,

Just a pretty basic question that I can't find the asnwer to:

I have a MySQL query of the following form:
Code: [Select]
$result = mysql_query (" SELECT $cols FROM tablename   WHERE.......");
Where $cols is as follows:
Code: [Select]
$cols = firstname, lastname, $price1, $price2, etc.
($price1 and $price2 are variable column names that contains prices)

How do I find the lowest value for $price1 and/or $price2 out of the results set of this query?

Thanks!