PHP - Select Statement/multiple Records/single User

This topic has been moved to MySQL Help.

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

Hello,

I had 8 select boxes(dropdowns) in a form. Its like a search kind of implementation in the website. I don't know what the user selects, he may choose different select boxes, any number of select boxes.

So, based upon the user selection, I need to generate the data. The data fields to be generated would be almost same.

So, How would I know what the user selection is and how many select boxes has been selected and how could I generate different data based upon selected boxes.

I had to make a small note abt this, that the data generation fields may change for some of the user select combinations, but most of the result fields would be same.

so, can you please help me out, how to do, how to make different combination data generations, because, i had 8 fields, i had to make 8! combinations, that would result in a big code.

Hi,

I need to compare records in a table based on the 'datetime' field, like if the difference between time is less than 15min.

need to combine the records(rows) as a single resultant row until the time difference between them  is less than 15min and when a record's time difference is >15min that should return as another row and so on. Please find following sample table and the required output format

Sample Table:

Code: [Select]
ID NAME DATETIME
1   aaa    2011-10-10 06:30:00
2   bbb    2011-10-10 06:33:00
3   ccc    2011-10-10 06:38:00
4   ddd    2011-10-10 06:40:00
5   eee    2011-10-10 07:10:00
6   ffff    2011-10-10 07:14:00
7   sss    2011-10-10 08:16:00
8   jjj    2011-10-10 08:26:00
9   kkk    2011-10-10 08:28:00
10  mm    2011-10-10 09:46:00
11  ppp    2011-10-10 09:49:00
12  qqq    2011-10-10 09:52:00


Output Needed :

Code: [Select]
IDs START DATETIME END DATETIME
1,2,3,4 2011-10-10 06:30:00 2011-10-10 06:40:00
5,6 2011-10-10 07:10:00                      2011-10-10 07:14:00
7,8,9 2011-10-10 08:16:00 2011-10-10 08:28:00
10,11,12 2011-10-10 09:46:00                      2011-10-10 09:52:00

You can see 1st row in the output table having IDs 1,2,3,4 coz the time difference between them is less than 15minutes (it doesn't means time difference between start and end; it is actually the  time difference between current record and previous one ; ie; ID-1 and ID-2 have difference less than 15min and ID-2 & ID-3 have difference less than 15min and ID-3 & ID-4 have difference less than 15min, so those 4 records combine together as a single row also shows their start time and end time. Then you can see diff between ID-4 and ID-5 is greater than 15 min so it should display as new row, and so on)

How can I acheive above Output with mysql query ??

Please help..

	$sql = "SELECT id,username, date1, date2, total 
	FROM datetable WHERE username = '".$_SESSION['uid']['username'] ."'";

Hello again, I ran into problem when trying to pull information for logged in user. I'm trying to display results for current user only who is signed in.

Now I do have session_start(); and my query works if I replace '".$_SESSION['uid']['username'] ."'"; with 'specificusername' but when I run my current query displayed above it returns me all records with username = empty (I have some empty usernames for testing reasons). So that tells me that query cant find unique user name that is logged in. I don't get any errors and query still goes through.

My datetable structure is 

 

My uid is defined in another document like so and uses another table called "users"

          session_start();
          $_SESSION['id'] = $row['idUsers'];
          $_SESSION['uid'] = $row['uidUsers'];
          $_SESSION['email'] = $row['emailUsers'];
          header("Location: ../member.php?login=success");
          exit();

This query works on another page where it displays logged in username in html document.

<?php echo $_SESSION['uid']; ?> 

 

I have tried many different ways yesterday to modify my request ( WHERE username = $_SESSION['uid'];  WHERE username = '".$_SESSION["uid"]) many other variations, but no matter what never returns logged in user information only empty username records. 

Can anyone see a problem with that query? or is it due to something else I'm missing?

 

Sorry, maybe this goes in MySQL thread not here in PHP page. If someone can, please move it. Thanks.

 

Edited April 25, 2020 by sfia
comment on where to post this topic

Hello:

I am trying to select all the records from a table, and display them.

Having an issue...

This is the code:
Code: [Select]
<ul>

<?php $query = mysql_query("SELECT id,mySectionTitle FROM myWebSiteData");
while($menuData = mysql_fetch_array($query))
{
<li><a href="a_websiteData.php?id=" .echo $menuData['id'] ."">echo $menuData['mySectionTitle']</a></li>
}
?>

</ul>

This is the error:
Code: [Select]
Parse error: syntax error, unexpected '<' in /html/admin/a_websiteData.php on line 91

line 91 is the one that starts with "<li>"

What am I missing, or what would be the best way to do this?

This topic has been moved to MySQL Help.

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

Hi,

I have db field with date. I want to make select statement with where clause when part of the date (month) is true. Example, i have records from 2020-2-1 until 2020-4-3, i want to select records for march only 2020-3-1 to 2020-3-31. how to do that? 

Hi Guys,

I am new to PHP/MYSQL. I am working on a web database project to record class attendance.
I have found this script which I have manipulated, however, I am not sure why it does not display the data. It may be something simple, I have checked over table names, field names, variable names to see that they match the database. It displays the textboxes with the php code rather than the data and all other fields display no data.

Can you help?

I will be focusing on Prepared Statements for SQL injections later.

<?php

$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name

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


$sql="SELECT * FROM attendance";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);


?>


<html>
<head>
<title>Registers</title>
</head>
<body>


<?php include 'Navigation.php';?>

<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">

<table border="0" cellspacing="1" cellpadding="0" style="width: 1461px; height: 105px">

<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Form</strong></td>
<td align="center"><strong>Year Group </strong></td>
<td align="center"><strong>Date</strong></td>
<td align="center"><strong>Attendance</strong></td>


</tr>

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

<tr>
<td align="center"><? $Student_id[]=$rows['Student_id']; ?><? echo $rows['Student_id']; ?></td>
<td align="center"><? $firstname[]=$rows['firstname']; ?><? echo $rows['firstname']; ?></td>
<td align="center"><? $lastname[]=$rows['lastname']; ?><? echo $rows['lastname']; ?></td>
<td align="center"><? $Form_Group[]=$rows['Form_Group']; ?><? echo $rows['Form_Group']; ?></td>
<td align="center"><? $Year_Group[]=$rows['Year_Group']; ?><? echo $rows['Year_Group']; ?></td>
<td align="center"><? $Att_Date[]=$rows['Att_Date']; ?><? echo $rows['Att_Date']; ?></td>
<td align="center"><input name="Presence[]" type="text" id="Presence" value="<? echo $rows['Presence']; ?>"></td>
</tr>

<?php
}
?>

<tr>
<td colspan="7" align="center"><input type="submit" name="Submit" value="Submit Register"></td>
</tr>
</table>

</form>
</table>


<?php

// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE attendance SET Presence='$Presence[$i]' WHERE Student_id='$Student_id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:Registers.php");
}
mysql_close();
?>

 

I have a problem with an amend to an admin system I am trying to change. The shop stocks different colours of each product and the client wants to be able to update the stock levels of all colours of one product (which all share the same title) on one form. I have tried following some instructions on some sites and tried making my own and I can get it to display the form and the values correctly, but on submission it does nothing but direct me to a page without a title value in the address (e.g. www.example.com/example/stock_level_2.php?title=). I think it may be to do with the arrays I am using, has anyone got any ideas? Here is the code.

Form:
Code: [Select]
<?php
$pd_title = str_replace("_"," ",$_REQUEST["title"]);
$pd_title = str_replace("%","&#38;#37;",$pd_title);
$pd_title = str_replace("/","&#38;#47;",$pd_title);

$lookupqueryc = mysql_query("SELECT * FROM product WHERE pd_title = '".$pd_title."' ORDER BY pd_id DESC");

echo '<table><tr><th align="center" class="bodytext">Product<br />ID</th><th align="center" class="bodytext">Product Name</th><th align="center" class="bodytext">Stock Level</th></tr>';

$form = '<form name="frmAmendStock" method="post" action="stock_level_2.php?title="';
$form .=  $_GET["title"];
$form .= '" enctype="multipart/form-data">';
echo $form;
echo '<input name="submitted" type="hidden" value="true">';
$count=mysql_num_rows($lookupqueryc);
echo '<input name="count" type="hidden" value="';
echo $count;
echo '">';

                          
  while($lookupresultc = mysql_fetch_array($lookupqueryc)){  
  
        

echo '<tr><td align="center" class="bodytext">';
                                $id[]=$lookupresultc["pd_id"];
echo $lookupresultc["pd_id"];
echo '</td><td class="bodytext">';
echo $lookupresultc["pd_title"];
if (isset($lookupresultc["pd_colour"]) && $lookupresultc["pd_colour"] !="") {
echo " (";
echo $lookupresultc["pd_colour"];
echo ')';
}
if ($lookupresultc["pd_stock"] <= 5) {
echo '</td><td align="center" class="bodytext" style="border: 2px solid red;">';
echo '<input name="stock[]" type="text" id="stock" value="';
                                echo $lookupresultc["pd_stock"];
                                echo '"style="width: 50px;" />'; 
echo "</td></tr>";
}else{
echo '</td><td align="center" class="bodytext">';
echo '<input name="stock[]" type="text" id="stock" value="';
                                echo $lookupresultc["pd_stock"];
                                echo '"style="width: 50px;" />'; 
echo "</td></tr>";

}



} ?>
<tr>
                <td>&nbsp;</td>
                <td height="35"><a href="javascript: " onClick="javascript:document.forms.frmAmendStock.submit()" class="bodytext"><strong>click here to amend your stock </strong></a></td>
              </tr>
</form> </table>


And here is the code it should run on submission:
Code: [Select]
<?php
if (isset($_POST["submitted"])) {

for($i=0;$i<$_POST["count"];$i++){
mysql_query("UPDATE product SET pd_stock='$stock[$i]' WHERE pd_id='$id[$i]'");
}

}
?>

Hi, I am trying to create an admin page for a local Gym Club to allow an Administrator to be able to update club prices which then show on different screens on the site.   I have been able to display the "Prices Admin" page which basically reads a MySQL database table (pricelist), display the description, member price and non-member price and allows the user to update any of these fields.   My problem is that when I try writing the data back to the database with the "UPDATE" statement it fails, what I mean is that nothing updates.  I have at the moment commented out the actual update statement and put in a "file_put_contents" command" to try and work out what is in the various fields before the UPDATE is run.  I find that the display/INPUT works perfectly well but when I do a foreach on the $record array variable I am getting strange results.   I may not have explained this very well but here are the relevant sections of my code;   // Display and Input section   <div id="admin-area">

This topic has been moved to MySQL Help.

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

have you ever wondered how to select a single coloumn from mysql using php & checking whether a value or variable exists in the resulting array... well here is the code

$busid = '5412';
$results    =   mysql_query("SELECT `myname` FROM mytable WHERE myuser = 'user'");
        while($rows = mysql_fetch_array($results))
        {   
            $colnames[] = $rows['myname'];
        }
        if(!in_array($busid, $colnames))die("Access denied");

hello guys moving on from inserting multiple records im now stuck with updating multiple records. i can get most of the details nailed but i need it to match the id's for the table the code i have in the sql part are
Code: [Select]
if (isset($_POST['submit'])) {
//Assign each array to a variable
$StaffMember = $_POST['StaffMember'];
$referrer = $_POST['referrer'];
$referred = $_POST['referred'];
$SentOut = $_POST['SentOut'];
$today = date("y.m.d H:i:s");
$user_id = $_SESSION['user_id'];
$IssueNum = $_POST['Referrerid'];
$limit = count($StaffMember);



$values = array(); // initialize an empty array to hold the values
for($k=0;$k<$limit;$k++){
$msg[] = "$limit New KPI's Added";



$values[$k] = "( '{$StaffMember[$k]}', '{$referrer[$k]}', '{$referred[$k]}', '{$SentOut[$k]}', '{$today}', '{$user_id}' )"; // build the array of values for the query string
}
$query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut,
  SentOutDate, SentOutBy)
  VALUES " . implode( ', ', $values ) . "
  WHERE IssueNum= '{$IssueNum[$k]}'";
 

echo $query;




}
i obviously want the records updating where the issuenumber is the same and if the check box in the form is ticked
help with this one?

This is the form that I'm using and it populates just fine but when you make the changes I can't figure out how to get it to update each record in my database.
Code: [Select]
<table width="100%" cellspacing="1" cellpadding="2" border="0">
<form action="<?php echo $_SERVER["PHP_SELF"] . "?update=1"; ?>" method="post">
<tr>
<td><b>Category Name</b></td>
<td><b>Category Description</b></td>
<td><b>Order</b></td>
<td align="right"><input type="submit" value="Update"></td>
</tr>
<?php
read_cat_list($cat);
for ($i = 0; $i < $cat["count"]; $i++) {
echo "<tr>\n";
echo "<td width=\"30%\" valign=\"top\"><input type=\"text\" name=\"cat_name_" . safe_string($cat[$i]["cat_id"]) . "\" size=\"30\" maxlength=\"250\" value=\"" . safe_string($cat[$i]["cat_name"]) . "\"></td>\n";
echo "<td width=\"36%\" valign=\"top\"><textarea name=\"cat_desc_" . safe_string($cat[$i]["cat_id"]) . "\" rows=\"2\" cols=\"30\">" . safe_string($cat[$i]["cat_desc"]) . "</textarea></td>\n";
echo "<td width=\"10%\" valign=\"top\"><input type=\"text\" name=\"cat_order_" . safe_string($cat[$i]["cat_id"]) . "\" size=\"3\" maxlength=\"3\" value=\"" . safe_string($cat[$i]["cat_order"]) . "\"></td>\n";
echo "<td width=\"24%\" valign=\"top\">[&nbsp;<a href=\"#\" onmouseover=\"window.status = 'Delete " . safe_string($cat[$i]["cat_name"]) . "'; return true;\" onmouseout=\"window.status = ''; return true;\" onclick=\"javascript:del_cat(" . $cat[$i]["cat_id"] . ", '" . safe_string($cat[$i]["cat_name"]) . "'); return false;\">Delete</a>&nbsp;]</td>\n";
echo "</tr>\n";
}
?>
<tr>
<td colspan="4" align="right"><input type="submit" value="Update"></td>
</tr>
</form>
</table>
the safe_string function just cleans up the output/input from the database, This next block is my form processor for this form.
Code: [Select]
<?php
function update_cats($vars) {
$err = "";

#if ($SESSION["level"] != ADMIN) {
# $err = ERR_NOT_ENOUGH_ACCESS;
#} else {
$temp = array_keys($vars);

for ($i = 0; $i < count($temp); $i++) {
if (substr($temp[$i], 0, 9) == "cat_name_") {
if ($vars[$temp[$i]] == "") {
$err = "Category names cannot be blank.";
break;
} else {
$name_query["cat_name"] .= substr($temp[$i], 9) . ", ";
}
}
}
for ($i = 0; $i < count($temp); $i++) {
if (substr($temp[$i], 0, 9) == "cat_desc_") {
$desc_query["cat_desc"] .= substr($temp[$i], 9) . ", ";
}
}
for ($i = 0; $i < count($temp); $i++) {
if (substr($temp[$i], 0, 10) == "cat_order_") {
if ($vars[$temp[$i]] == "") {
$err = "Category orders cannot be blank.";
break;
} else {
$order_query["cat_order"] .= substr($temp[$i], 10) . ", ";
}
}
}
#}

if (!$err) {
if ($name_query["cat_name"]) {
$update_name_query  = "update category";
$update_name_query .= "   set cat_name = '" . $name_query["cat_name"] . "'";
$update_name_query .= " where cat_id in (" . substr($name_query["cat_name"], 0, -2) . ")";

update_db($update_name_query);
}

if ($desc_query["cat_desc"]) {
$update_desc_query  = "update category";
$update_desc_query .= "   set cat_desc = '" . $name_query["cat_desc"] . "'";
$update_desc_query .= " where cat_id in (" . substr($desc_query["cat_desc"], 0, -2) . ")";

update_db($update_desc_query);
}

if ($order_query["cat_order"]) {
$update_order_query  = "update category";
$update_order_query .= "   set cat_order = '" . $name_query["cat_order"] . "'";
$update_order_query .= " where cat_id in (" . substr($order_query["cat_order"], 0, -2) . ")";

update_db($update_order_query);
}
}

return $err;
}
?>
update_db is my database caller, if you need any of the functions that I use that are not here please post back and tell me.

now when I process the form all my fields change to this:

cat_name: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
cat_desc:
cat_id: 0

for all the records in the database table, I'm just trying to update each record with the values it needs

Here is my database table with the data.
Code: [Select]
CREATE TABLE `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(255) NOT NULL,
  `cat_desc` text NOT NULL,
  `cat_order` int(11) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(1, 'Hand Tossed Pizza', '', 1);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(2, 'Hand Tossed Specialty Pizzas', '', 2);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(3, 'Chicago Style Deep Dish', '', 3);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(4, 'Specialty Chicago Style Deep Dish', '', 4);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(5, 'Chicken Wings & Tenderloins', '', 5);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(6, 'Hot Sides', '', 6);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(7, 'Hot Sandwiches', '', 7);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(8, 'Cold Sandwiches', '', 8);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(9, 'Pastas', '', 9);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(10, 'Fresh Salads', '', 10);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(11, 'Fresh Breads', '', 11);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(12, 'Soups', '', 12);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(13, 'Kids Menu', '', 13);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(14, 'Drinks', '', 14);
INSERT INTO `category` (`cat_id`, `cat_name`, `cat_desc`, `cat_order`) VALUES(15, 'Desserts', '', 15);

Hope I have provided plenty of info on what I got and what I need for it todo

Thanks

Hi I'm using this code to insert multiple records. The code executes but nothing is entered into my database. This usually happens when there's a mismatch in data types.   How do I ensure that description goes in as text which in sql is wrapped in single quotes, but also make sure the other variables go in as numeric.

 // an array items to insert
    $array = array( 'theid'   => $theid,
            'descr'      =>   $descr,
            'costperunit'    => $costperunit,
            'quantity'       => $quantity,
'costperlot'       => $costperlot
            );


    // begin the sql statement
    $sql1 = "INSERT INTO  descriptions (jobid, description, costperunit, quantity, costperlot) VALUES ";

    $it = new ArrayIterator( $array );


    // a new caching iterator gives us access to hasNext()
    $cit = new CachingIterator( $it );


    // loop over the array
    foreach ( $cit as $value )
    {
        // add to query
        $sql1 .= "('".$cit->key()."','" .$cit->current()."')";
       

        if( $cit->hasNext() )
        {
            $sql1 .= ",";
        }
    }