PHP - Select Multiple Columns From Two Tables For Searching

I'm having a hard time grasping how to select data from several tables.

Here's the code

Code: [Select]
<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />";

$umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name');

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

foreach($umpirelist as $data) { 

//now get stuff from a table           
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; 

$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[$data]<br />";
}
}
?>

The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game.  it does have a unique "row_number" column as well, but didn't think I'd need it in this select.

The table VACATION has the date, and umpire's name that is on vacation.

The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info.

I'm getting this error:

Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152



This is line 152

Quote

while ($row = mysql_fetch_assoc($rs))  {



Can someone please point me in the right direction as to where I'm going wrong?

Thanks!

hi im trying to echo out all the possibilities from one column in 2 different tables, both with the same structure. if that makes sense

this is what i have

$query = mysql_query("SELECT * FROM stanjamesprem,centrebetprem GROUP BY eventname ORDER BY eventtime ");
while($row = mysql_fetch_assoc($query))
   {
   echo $row[eventname];
   }

if i take one of the tables out it works fine but if i have 2 table names it doenst work

please help me

cheers matt

any questions please ask away

I want to select the user from super_administrators, administrators, teachers and students, and give the user permission based from what table he "came". But is giving the "Query failed" error...

Code: [Select]
<?php

//Start session

session_start();



//Include database connection details

require_once('../config/config.php');



//Array to store validation errors

$errmsg_arr = array();



//Validation error flag

$errflag = false;



//Connect to mysql server

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link) {

die('Failed to connect to server: ' . mysql_error());

}



//Select database

$db = mysql_select_db(DB_DATABASE);

if(!$db) {

die("Unable to select database");

}



//Function to sanitize values received from the form. Prevents SQL injection

function clean($str) {



$str = @trim($str);



if(get_magic_quotes_gpc()) {

$str = stripslashes($str);

}



return mysql_real_escape_string($str);

}



//Sanitize the POST values

$email = clean($_POST['email']);

$password = clean($_POST['password']);



//Input Validations

if($email == '') {

$errmsg_arr[] = 'O campo Email nao foi preenchido.';

$errflag = true;

}



if($password == '') {

$errmsg_arr[] = 'O campo Senha nao foi preenchido.';

$errflag = true;

}



//If there are input validations, redirect back to the login form

if($errflag) {

$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

session_write_close();

header("location: ../index.php");

exit();

}



//Create query

$qry = "SELECT * FROM super_administrators,administrators,teachers,students WHERE email = '$email' AND passwd = '".md5($_POST['password'])."'";

$result = mysql_query($qry);

$member = mysql_fetch_assoc($result);

$table = mysql_field_table('$result', '0');



//Check whether the query was successful or not



if($result) {



if(mysql_num_rows($result) == 1) {



if($table == 'super_administrators')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/sadmin/index.php");

exit();

}



if($table == 'administrators')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/admin/index.php");

exit();

}



if($table == 'teachers')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/prof/index.php");

exit();

}



if($table == 'students')  {

session_regenerate_id();

$_SESSION['SESS_ID'] = $member['id'];

$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

$_SESSION['SESS_CLASS_ID'] = $member['class_id'];

$_SESSION['SESS_NAME'] = $member['name'];

$_SESSION['SESS_REGISTRATION'] = $member['registration'];

$_SESSION['SESS_EMAIL'] = $member['email'];

session_write_close();

header("location: ../users/aluno/index.php");

exit();

}



} else {



$errmsg_arr[] = 'Suas informacoes de login estao incorreta. Por favor, tente novamente.';

$errflag = true;

$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

session_write_close();

header("location: ../index.php");

exit();

}



} else {

die("Query failed");

}

?>

This topic has been moved to MySQL Help.

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

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.

Hi, I have a piece of code which I trying to search two columns but I just cant get it to work.  Instead it just dumps around 30 entries onto the page.  Can anyone point me in the direction of how to search two columns?

Code: [Select]
$query = "SELECT * FROM questions";

if(isset($_GET['question']) && !empty($_GET['notes'] ))
{
/*query the database*/
$question = $_GET['question'];
$notes = $_GET['notes'];
$query .= " WHERE question && notes like '%$question%' LIMIT 0, 10";
}

$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))

{

$question = $row['question'];
$notes = $row['notes'];

echo "$question
$notes  </br>";
}

This topic has been moved to MySQL Help.

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

Hi, does anybody know whether it is possible to search 2 tables from a database at once?

Hello all,

   I am trying to learn OOP in PHP so please forgive my ignorance. I seem to be having difficulty inserting data into my database from checkbox items. My database has a column for each each fruit. I would like for each column to be populated with either a one(1) for checked or a zero(0)  for not checked. Currently, my attempts have failed. I can either get all checkboxes to insert all zeros or I can get the first column to insert a one(1) while the others as zeros. Is there anybody here that can help me figure out what I am doing wrong and help me to re-work my code so I can get this to work? If anybody can help me using OOP methods that would be fantastic. Thank you all in advance.

 

$preffruit->create_preffruit(array(

  'fruit_apple' => escape(Input::get(['fruit_selection'][0]) ? 1 : 0),
  'fruit_orange' => escape(Input::get(['fruit_selection'][1]) ? 1 : 0),
  'fruit_banana' => escape(Input::get(['fruit_selection'][2]) ? 1 : 0),
  'fruit_pear' => escape(Input::get(['fruit_selection'][3]) ? 1 : 0),
  'fruit_kiwi' => escape(Input::get(['fruit_selection'][4]) ? 1 : 0)

  ));

<input type="checkbox" name="fruit_selection[]" value="fruit_apple">
<input type="checkbox" name="fruit_selection[]" value="fruit_orange">
<input type="checkbox" name="fruit_selection[]" value="fruit_banana">
<input type="checkbox" name="fruit_selection[]" value="fruit_pear">
<input type="checkbox" name="fruit_selection[]" value="fruit_kiwi">



public function insert_preffruit($table, $fields = array()) {
			
			$keys = array_keys($fields);
			$values = '';
			$x = 1;			
			
			foreach($fields as $field) {
				$values .= '?';
				if($x < count($fields)) {
					$values .= ', ';
				}
				$x++;
			}		
				
				$sql = "INSERT INTO preffruit (`user_id`, `" . implode('`, `', $keys) . "`) VALUES (LAST_INSERT_ID(), {$values})";		
			
			if(!$this->query($sql, $fields)->error()) {
				return true;
			}			
			
			return false;
		}

 

Edited September 23, 2020 by ke-jo

This topic has been moved to MySQL Help.

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

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??
}
?>

I want to echo out a few subcategories per main category.
It should be a maximum of 20 entries per column

At this stage I have the following... It echo's the results out in 4 columns and not what I want.
Any suggestions?

$query = "SELECT 
adsubcat.name AS subname, adsubcat.linkname AS sublink, adcat.clinkname AS clink
FROM adsubcat 
JOIN adcat ON adcat.id=adsubcat.catid
WHERE adsubcat.catid='$catid' ORDER BY adsubcat.name ASC";
$result = mysql_query($query) or die(mysql_error());

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

$count = 0;

print '<table style="text-align:left;margin-left:0px;width:943px;">'."\n";
while ($row = mysql_fetch_assoc($result)){
$gal = '<a href="'.$root.'/'.$row['clink'].'/'.$row['sublink'].'">'.$row['subname'].'</a>';
if ($count == 0){
print '<tr>';
}
++$count;
print '<td>'.$gal.'</td>';
if ($count == 4){
$count = 0;
print'</tr>'."\n";
}
}
print'</table>'."\n";
}

First off, hello all!

I want to improve our website's search but unfortunately don't know enough to get there!

I'm not a PHP programmer but I'm generally able to tweak things... 

Our search page has 3 options - All words, Any word, Exact. 

I want to concentrate on the default "All words" which is the default parameter.

The code is essentially as follows -

Code: [Select]
$gotcriteria=TRUE;
$Xstext = mysql_escape_string($stext);
$aText = split(" ",$Xstext);
$aFields[0]="products.pId";
$aFields[1]="products.pName";
$aFields[2]="products.pDescription";
$aFields[3]="products.pLongDescription";
if($stype=="exact")
$sSQL .= "AND (products.pId LIKE '%" . $Xstext . "%' OR ".getlangid("pName",1)." LIKE '%" . $Xstext . "%' OR ".getlangid("pDescription",2)." LIKE '%" . $Xstext . "%' OR ".getlangid("pLongDescription",4)." LIKE '%" . $Xstext . "%') ";
else{
$sJoin="AND ";
if($stype=="any") $sJoin="OR ";
$sSQL .= "AND (";
for($index=0;$index<=3;$index++){
//$sSQL .= "(";
$rowcounter=0;
$arrelms=count($aText);
foreach($aText as $theopt){
if(is_array($theopt))$theopt=$theopt[0];
$sSQL .= $aFields[$index] . " LIKE '%" . $theopt . "%' ";
if(++$rowcounter < $arrelms) $sSQL .= $sJoin;
}
//$sSQL .= ") ";
if($index < 3) $sSQL .= "OR ";
}
$sSQL .= ") ";
}

Here's the important bit - as far as I can see, the search looks at each of the 4 fields in the array individually?  So if the search text is "RED APPLE", to get a result for the "All Words" option will require that both "RED" AND "APPLE" will need to be present in one of the searched fields...?

What I want to achieve is that if the product name is "RED FRUIT" and the description mentions "APPLE", then an "All words" search for "RED APPLE" will pick this up.

I'd guess that the easiest way would be to string the table fields together for searching but I don't know how to do this as a query... now I'm thinking I'll get flamed for not posting this in the MySQL forum too!! 

Thanks in advance for any help!

I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player?

This is what I'm trying now:

Code: [Select]
/* CONNECTION VARIABLES */

$id = $_GET['id']; // get var from URL


/* Get data. */
$sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'";
$result = mysql_query($sql);

?>


<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["season"];
$field2 = $row["team"];
$field3 = $row["games"];
$field4 = $row["ave"];
$field5 = $row["slg"];
$field6 = $row["r"];
$field7 = $row["h"];
$field8 = $row["rbi"];
$field9 = $row["bb"];
$field10 = $row["k"];
$field11 = $row["hr"];
$field12 = $row["dbl"];
$field13 = $row["tpl"];
$field14 = $row["sb"];
$field15 = $row["obp"];
$field16 = $row["ops"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>";
}
echo "</table>";
?>
Thank you in advance!

This seems to be a bit of a challenge but I am creating a multiple page form and on one of the pages I have a select field. I would like the user to have the ability to select multiple options but I am using some functions to move the data in hidden fields from page to page. I don't think my functions are jiving with my my foreach loop cause I keep getting an invalid argument error.

Thanks in advance for any help.

Here is my function:

    function setSelected($fieldName, $fieldValue) {
        if(isset($_POST[$fieldName]) && $_POST[$fieldName] == $fieldValue) {
            echo 'selected="selected"';
        }
    }

And here is my loop:

    $selections = "";
   
    if(isset($_POST["selections"])) {
        foreach ($_POST["selections"] as $selection) {
            $selections .= $selection . ", ";
        }
    }