PHP - Retrieve Records From Several Tables Where Certain_col = Certain_value

I am having trouble showing reports for a given date range. Currently if I specify something like 11/03/2010 to 11/05/2010 I get results for all years within that month and day such as I may get results for 11/03/2008 11/03/2009 11/03/2010 11/04/2008 11/04/2009 11/04/2010 11/05/2008 11/05/2009 11/05/2010.
I am using the following code
$result = mysql_query("SELECT * FROM report WHERE date>='$date_begin' and date<='$date_end' ORDER BY 'date'",$db);

I use the following format in my date feild mm/dd/yyyy

My script is finally working as intended, but I want to add some additional data results.

I am trying to resolve how I can display a count of ONLY the records updated by my query:

// START :: Query to replace matches
mysql_query("UPDATE orig_codes_1a AS a
      JOIN old_and_new_codes_1a AS b ON concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2)
   SET a.orig_code_1 = b.new_code_1,
                           a.orig_code_2 = b.new_code_2") or die(mysql_error());
// END :: Query to replace matches


In this query I count ALL records selection:


// START :: Create query to be displayed as final results of original codes table.
$result = mysql_query("SELECT * FROM orig_codes_1a") 
or die(mysql_error());  

I want to display a count on this part of the query:

....concat(a.orig_code_1, a.orig_code_2) = concat(b.old_code_1, b.old_code_2)....

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

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

I am trying to write some data from multiple SQL tables to a page. In the first table is a list of places. I then have more tables that are named after the different places. For example, say my first place in the list is called Place1. I have a table named Place1 with data that corresponds to place1. The data contained in the table named Place1 is a list of things to do in this place. It has 21 columns and each one is something to do in the morning, afternoon, and at night for each day of the week in the place Place1. What I am trying to do is display a sort of weekly calendar as a table on a webpage that lists all of the places in one column and then lists seven days of the week as 7 more columns. Then in each data cell I would want to list the things to do in the morning, afternoon and at night for the certain day of the week and for the place. The problem is that I am creating a CMS to allow other users with no coding knowledge to update events for other places, so I have to display data that could have been altered.

The only solution I know of is to do a while loop that gets all of the place names and while there are still place names, write the place names to the page and set a variable equal to the place name. Inside the while loop I would create another while loop that each time the first while loop is executed uses the variable set in the first while loop to know which table to reference and then make a call to that table pulling out the 21 columns and writing them to the page. Each time the outer while loop executes, it would (hopefully) write the place name, and then set the variable as the current place name so that the inner while loop uses the variable to write the rest of the information about that place.

I don't know if that would even work and if it did, I know it cannot be the best way to do this. I am pretty stuck here and don't really have a good solution so if anyone proposes a solution that is radically different to anything I have done, I am open to everything. Thank you!

I have a form that ask user to enter other details but the primary key as this is automated and when they click on Submit button it takes the user to different form. now on this form I would like to retreive the primary that has just been added to the table. How can I do this?

Hi,

I am not very good with php but this code was kindly given to me by mdjamato, except I added a variable $markGradeSum to add all the $markGrade. Problem is that I will get notice saying undefined variable $markGradeSum because the $markGradeSum is in the foreach loop which it needs to be in for the right calculation but where I want to output the variable is in the while loop in this line:

Code: [Select]
$output .= "<br><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <strong>Course Mark</strong> <strong>Grade</strong>  <br><strong>Year:</strong> {$row['Year']  $markGradeSum}</p>\n";
Below is whole code:

Code: [Select]
<?php
     
     if($num ==0){
    echo "<p>Sorry, No Records were found from this Search</p>";}
    else{
    
  function outputModule($moduleID, $moduleName, $sessionData)    
  
  {        
  
  if(!count($sessionData)) { return false; }        
  
  $markTotal = 0;        
  $markGrade = 0;
  $weightSession = 0;
  $markGradeSum = 0;
  $grade = "";
  $sessionsHTML = "";
  $courseHTML = "";
  
  foreach($sessionData as $session)        {           
  
   $sessionsHTML .= "<p><strong>Session:</strong> {$session['SessionId']} <strong>Session Mark:</strong> {$session['Mark']}</strong> <strong>Session Weight Contribution</strong> {$session['SessionWeight']}%</p>\n";            
   $markTotal += round($session['Mark'] / 100 * $session['SessionWeight']); 
   $weightSession  += ($session['SessionWeight']);  
   $markGrade = round($markTotal /  $weightSession * 100);
   $markGradeSum = round($markGrade);
   
if ($markGrade >= 70){
$grade = "A";}      

else if ($markGrade >= 60 && $markGrade <= 69){
$grade = "B";}

else if ($markGrade >= 50 && $markGrade <= 59){
$grade = "C";}

else if ($markGrade >= 40 && $markGrade <= 49){
$grade = "D";}

else if ($markGrade >= 30 && $markGrade <= 39){
$grade = "E";}

else if ($markGrade >= 0 && $markGrade <= 29){
$grade = "F";}
   
   }        
   
   $moduleHTML = "<p><br><strong>Module:</strong> {$moduleID} - {$moduleName} <strong>Module Mark:</strong> {$markTotal} <strong>Mark Percentage:</strong> {$markGrade} <strong>Grade:</strong> {$grade} </p>\n";       
   
    return $moduleHTML . $sessionsHTML;    }   
    
    $output = "";    
    
    $studentId = false;    
    $courseId  = false;    
    $moduleId  = false;    
    
    while ($row = mysql_fetch_array($result))    {      
    
         
     
     if($studentId != $row['StudentUsername'])        {            
     
     //Student has changed              
     $studentId = $row['StudentUsername'];                    
     
     $output .= "<p><strong>Student:</strong> {$row['StudentForename']} {$row['StudentSurname']} ({$row['StudentUsername']})\n";          
     
      }        
      
     if($courseId != $row['CourseId'])        {         
      
      //Course has changed           
       $courseId = $row['CourseId'];                    
       
       $output .= "<br><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <strong>Course Mark</strong> <strong>Grade</strong>  <br><strong>Year:</strong> {$row['Year']  $markGradeSum}</p>\n";           
       
        }      
        
 if($moduleId != $row['ModuleId'])        {            
 
 //Module has changed            
 if(isset($sessionsAry)) //Don't run function for first record           
 
  {              
  
    //Get output for last module and sessions                
    $output .= outputModule($moduleId, $moduleName, $sessionsAry);            }            
    
    //Reset sessions data array and Set values for new module           
    
     $sessionsAry = array();            
     $moduleId    = $row['ModuleId'];         
     $moduleName  = $row['ModuleName'];        }        
     
     //Add session data to array for current module      
     
     $sessionsAry[] = array('SessionId'=>$row['SessionId'], 'Mark'=>$row['Mark'], 'SessionWeight'=>$row['SessionWeight']);    
     
     }    //Get output for last module  
     
      $output .= outputModule($moduleId, $moduleName, $sessionsAry);   
      
       //Display the output    
       echo $output;

      }
  }
    ?>

How can I retrieve the variable so it doesn't come up with the notice but be able to display $markGradeSum to the places I want them in (one in foreach loop for calculation and other in while loop for output).

Thank You

Hello, I have a booking system which has time and date fields and if someone tries to book, for example, on 01/01/2011 at 09:00, when this slot is already taken, an error would be produced - which is fine. However I'm not sure in telling them what hours are available.

There are 9 available times: 09,10,11,12,13,14,15,16,17:00.

I will need to perform an sql query to gather all the times on the inputted date, I'm guessing a for loop would be the most efficient way?

The only way I could think of is using multiple IF queries, for example:

Code: [Select]
<?php
$sql = "SELECT `Hour` FROM `jobs` WHERE `Hour` = '09:00:00' AND Day ='$Day' AND Month ='$Month' AND Year ='$Year'";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0){
$09Available = 'n'; // Time not available
}else{
$09Available = 'y';
}

$sql = "SELECT `Hour` FROM `jobs` WHERE `Hour` = '10:00:00' AND Day ='$Day' AND Month ='$Month' AND Year ='$Year'";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0){
$10Available = 'n'; // Time not available
}else{
$10Available = 'y';
}

$sql = "SELECT `Hour` FROM `jobs` WHERE `Hour` = '11:00:00' AND Day ='$Day' AND Month ='$Month' AND Year ='$Year'";
$result = mysql_query($sql);
if(mysql_num_rows($result) > 0){
$11Available = 'n'; // Time not available
}else{
$11Available = 'y';
}

?>

etc. etc..

How would I go about creating a more efficient method?

Thank you, Jack.

I have a variable $form in the code below what I cant figure out how do it is call it and get the information it has out so I can display the errors:

Code: [Select]
function login($subuser, $subpass, $subremember){
      global $database, $form;  //The database and form object

      /* Username error checking */
      $field = "user";  //Use field name for username
      if(!$subuser || strlen($subuser = trim($subuser)) == 0){
         $form->setError($field, "* Username not entered");
      }
      else{
         /* Check if username is not alphanumeric */
         if(!preg_match("^([0-9a-z])*$^i", $subuser)){
            $form->setError($field, "* Username not alphanumeric");
         }
      }
     
      if ($database->usernameBanned($subuser))
      {
      $form->setError($field, "*Your Account has been banned");
      }

      /* Password error checking */
      $field = "pass";  //Use field name for password
      if(!$subpass){
         $form->setError($field, "* Password not entered");
      }
     
      /* Return if form errors exist */
      if($form->num_errors > 0){
         return false;
      }

      /* Checks that username is in database and password is correct */
      $subuser = stripslashes($subuser);
      $result = $database->confirmUserPass($subuser, md5($subpass));

      /* Check error codes */
      if($result == 1){
         $field = "user";
         $form->setError($field, "* Username not found");
      }
      else if($result == 2){
         $field = "pass";
         $form->setError($field, "* Invalid password");
      }
     
      /* Return if form errors exist */
      if($form->num_errors > 0){
         return false;
      }

Hi,

Is it possible to retrieve all the information from a posted value without knowing the input field?

My page is taking information from a database and then creates a page with checkboxs so that people can select certain fields. There are 1300 fields in the database so I don't know what fields will be submitted when a person saves the page,  is there a simple way to check what fields have been submitted without writing a check for each checkbox?

I hope this makes sense, thanks in advance for any help.

Dear All,

I have login script already. i have two database one is users and another one is reference  in both user_name is common. In this if a user login and search the refid means that user datas onle possible to retrieve. for others datas they cant access. i wrote the below code but it display all user datas. kindly help me


<?php
session_start();

if (!$_SESSION["user_name"])
        {
        // User not logged in, redirect to login page
        Header("Location: login.php");
  /     }

// Member only content
// ...
$con = mysql_connect('localhost','root','');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("login", $con);


$user_name = $_POST['user_name'];


$query = "select * from reference,users where  reference.user_name=users.user_name and reference.refid='$refid'";
$result = mysql_query($query) or trigger_error('MySQL encountered a problem<br />Error: '  . mysql_error() . '<br />Query: ' . $query);


 while($row = mysql_fetch_array($result))
  {
  echo $row['refid'];
  echo $row['origin'];
  echo $row['dest'];
  echo $row['date'];
  echo $row['exdate'];
  echo $row['user_name'];
  }


echo "<p><a href=\"logout.php\">Click here to logout!</a></p>";
?>


<html>
<form method="post" action="final.php">
Ref Id:<input type="text" name="refid">
<input type="submit" value="submit" name="submit">
</html>

What php method is good for getting file from a .xml sheet ?

Hi!
I am trying to implement the comment feature.
After a user selects a recipe from the search results (http://razor.ischool.utexas.edu/~nem/project/index.php scroll down to choose a recipe from the search results), the recipe page is displayed. The user may leave a comment at the bottom of the recipe page if she wishes to. The comment would be recorded in a mysql table and be displayed along with other comments for this particular recipe.

The problem is that when the user has entered the comment and name and hits "Add my comment", this error appears: Notice: Undefined index: id in /.../recipeText_w.php on line 57 error retrieving proteins data

While I understand it happens because the recipe Id ($_GET['id']) becomes unavailable after the comment and name are submitted, I am not sure how to make the recipe ID available again so that the same recipe page is displayed.

The script is attached. I will appreciate your advice.

I have two pages - add_product.php and show.php

here is the code for add_product.php

Code: [Select]
<?php

ini_set('display_errors',1);
error_reporting(-1);

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

require_once (MYSQL);

$add_cat_errors = array();

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
// Check for a name:
if (empty($_POST['product'])) {
$add_cat_errors['product'] = 'Please enter the name!';
}

// Check for a description:
if (empty($_POST['prod_descr'])) {
$add_cat_errors['prod_descr'] = 'Please enter the description!';
}

// Check for a category:
if (!isset($_POST['cat']) || !filter_var($_POST['cat'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
$add_cat_errors['cat'] = 'Please select a category!';
}

// Check for a price:
if (empty($_POST['price']) || !filter_var($_POST['price'], FILTER_VALIDATE_FLOAT) || ($_POST['price'] <= 0)) {
$add_cat_errors['price'] = 'Please enter a valid price!';
}

// Check for a category:
if (!isset($_POST['directory']) || !filter_var($_POST['directory'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
$add_cat_errors['directory'] = 'Please select a directory!';
}

// Check for a stock:
if (empty($_POST['stock']) || !filter_var($_POST['stock'], FILTER_VALIDATE_INT, array('min_range' => 1))) {
$add_cat_errors['stock'] = 'Please enter the quantity in stock!';
}

if (empty($add_cat_errors))
{
$query = "INSERT INTO product (product, prod_descr, catID, price, dirID, stock) VALUES (?, ?, ?, ?, ?, ?)";
// Prepare the statement:
$stmt = mysqli_prepare($dbc, $query);
// For debugging purposes:
// if (!$stmt) echo mysqli_stmt_error($stmt);

// Bind the variables:
mysqli_stmt_bind_param($stmt, 'sssssi', $name, $desc, $_POST['cat'], $_POST['price'], $_POST['directory'], $_POST['stock']);

// Make the extra variable associations:
$name = strip_tags($_POST['product']);
$desc = strip_tags($_POST['prod_descr']);
// Execute the query:
mysqli_stmt_execute($stmt);

if (mysqli_stmt_affected_rows($stmt) == 1) { // If it ran OK.

// Print a message:
echo '<h4>The product has been added!</h4>';

// Clear $_POST:
$_POST = array();

// Clear $_FILES:
$_FILES = array();


} else { // If it did not run OK.
trigger_error('The product could not be added due to a system error. We apologize for any inconvenience.');
}

} // End of $errors IF.

} else { // Clear out the session on a GET request:
} // End of the submission IF.

require_once ('./includes/form_functions.inc.php');
?>

<form enctype="multipart/form-data" action="add_image.php?prodID={$row['prodID']}" method="post" accept-charset="utf-8">

<input type="hidden" name="MAX_FILE_SIZE" value="524288" />

Product<br /><?php create_form_input('product', 'text', $add_cat_errors); ?>
           
            Description<br /><?php create_form_input('prod_descr', 'textarea', $add_cat_errors); ?>
           
Category<br /><select name="cat"<?php if (array_key_exists('cat', $add_cat_errors)); ?>>
<option>Select One</option>
<?php // Retrieve all the categories and add to the pull-down menu:
$q = 'SELECT catID, cat FROM category ORDER BY cat ASC';
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
echo "<option value=\"$row[0]\"";
// Check for stickyness:
if (isset($_POST['cat']) && ($_POST['cat'] == $row[0]) ) echo ' selected="selected"';
echo ">$row[1]</option>\n";
}
?>
</select><?php if (array_key_exists('cat', $add_cat_errors)) echo $add_cat_errors['cat']; ?>
           
            Price<br /><?php create_form_input('price', 'text', $add_cat_errors); ?>
           
            Directory<br /><select name="directory"<?php if (array_key_exists('directory', $add_cat_errors)); ?>>
<option>Select One</option>
<?php // Retrieve all the categories and add to the pull-down menu:
$q = 'SELECT dirID, directory FROM directory ORDER BY directory ASC';
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
echo "<option value=\"$row[0]\"";
// Check for stickyness:
if (isset($_POST['directory']) && ($_POST['directory'] == $row[0]) ) echo ' selected="selected"';
echo ">$row[1]</option>\n";
}
?>
</select><?php if (array_key_exists('directory', $add_cat_errors)) echo $add_cat_errors['directory']; ?>
</select>

          <br />

Stock<br /><?php create_form_input('stock', 'text', $add_cat_errors); ?>


<input type="submit" value="Add This Product" class="button" />


</fieldset>

</form>

and here is the code for show.php

Code: [Select]
<?php

ini_set('display_errors',1);
error_reporting(-1);

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

require_once (MYSQL);

$add_cat_errors = array();

if($id = isset($_GET['prodID']))
{
$q = "SELECT `prodID`, `product`, `prod_descr`, `catID`, `dirID`, `price`, `stock` FROM product WHERE `prodID`='{$_GET['prodID']}'";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r))
{
echo $row['product'];
echo "<br />";
echo $row['prod_descr'];
echo "<br />";
echo $row['catID'];
echo "<br />";
echo $row ['dirID'];
echo "<br />";
echo $row['price'];
echo "<br />";
echo $row['stock'];
}
}


What I want is to try and retrieve the id of the last record inserted in add_product and pass it into show.php, but I get this error message.

Quote

An error occurred in script 'C:\Users\David Morgan\Desktop\WEBSITES\hairz_&_graces\site\admin\add_image.php' on line 16:
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

I am struggling to find out where I am going wrong at the moment, if anyone could please help me that would be really appreciated.