Search the Community

Hi, I want to make a select from mysql to show some data in page. To show a table with data from mysql but limited to only 5 and this to show total number of rows. I'm thinking to make two selects. SELECT count(*) FROM table1 and after that to use mysql_num_rows to count total number in that table. Then another SELECT name,prename FROM table1 LIMIT 1, 5 to show only first 5 rows. It's there a way to use only one select insted of two but having the same results ? Thanks

I am trying to use a <select> <option> to display text on a web page. The data comes from a database. This is what I am attempting: When an option is selected and submit button clicked them some text will be displayed. At the moment I do have text on the web page, but none of the selected options change this. I have four pieces of text: Page 1, Page 2 etc. At the moment Page 4 text is displayed. I would like each piece of text to be displayed. This is the php: <?php // Connect to the database $pdo = new PDO("mysql:host=localhost;dbname=###", "###", ""); $sql = "SELECT * FROM testdb ORDER BY id"; try { $stmt = $pdo->prepare($sql); $stmt->execute(); $data = $stmt->fetchAll(); } catch(Exception $ex){ echo ($ex -> getMessage()); } ?> This is the html: <form name="###" method="post" action="#"> <p></p> <select onchange="reload(this.form)"> <option>test one</option> <?php foreach ($data as $output) { ?> <option value=''><?php echo $output['header']; ?></option> <?php } ?> </select> <br> <button type="submit" value="submit">Submit</button> </form> <?php echo $output['pages']; ?> I would appreciate help with this. However, from previous attempts at adding data to a page I used isset() and I think $_POST(), possibly together. If these are the things I need to use, then please could you include their usage within any examples you feel would help. Thank you.

I have a timeclock application where you can add entries into a table. One of the columns is a <select> dropdown tag. If you do not enter one of the required fields, I redisplay the values entered so the user can correct the entries and resubmit to save them. Everything saves (posts) except the <select> dropdown value. The dropdown does show properly, even with the properly selected value. But if you change the value, it doesn't update. It retains the original value. Here is the function from STOP_Timeclock.php class that does the form: public function add_entries_page($msg) { $redisp = $_SESSION['redisp']; $out = ''; $offhrs = "N"; $dbh = new Database; //display the last entry the user saved to the database $dbh->query("SELECT * FROM Entries WHERE UID = :uid and GID = :gid order by Date DESC Limit 1"); $dbh->bind(':uid', $_SESSION['UID']); $dbh->bind(':gid', $_SESSION['GID']); $rows = $dbh->resultset(); foreach ($rows as $value) { $msg .= "Your last entry was: " . date('m-d-Y',strtotime($value['Date'])) . " for: " . $value['Description'] . " - " . number_format($value['Hours'],2,'.','') . " hour(s) were recorded. <br>"; } $select = $this->getGroupsForEntriesForm($_SESSION['UID']); //$select2 = $this->getGroupsForEntriesForm3($_SESSION['UID'],$_SESSION['array_category']); $caption = 'If you leave the Date Box empty, it defaults to today. *<b>Date/Category/Description/Hours are REQUIRED fields</b> <br><br>'; $head = array(array('name' => 'head', '1' => 'Date *', '2' => 'Ticket ID', '3' => 'Ticket Name', '4' => 'Category *', '5' => 'Description of Work Done *', '6' => 'Hours *', '7' => 'Off Hours(Y/N)' )); if($redisp == 0) { $body = array(); unset($_SESSION['array_id']); unset($_SESSION['array_name']); unset($_SESSION['array_category']); unset($_SESSION['array_desc']); unset($_SESSION['array_hours']); unset($_SESSION['array_offhrs']); for ($i = 1; $i < 11; $i++) { $row = array($this->xhtml->input(array('type' => 'text', 'id' => "datepicker$i", 'class' => 'entries_field_1', 'name' => 'DATE[]' )), $this->xhtml->input(array('type' => 'text', 'name' => 'ID[]', 'class' => 'entries_field_2' )), $this->xhtml->input(array('type' => 'text', 'name' => 'NAME[]', 'class' => 'entries_field_3' )), $select, $this->xhtml->input(array('type' => 'text', 'name' => 'DESC[]', 'class' => 'entries_field_5' )), $this->xhtml->input(array('type' => 'text', 'name' => 'HOURS[]', 'class' => 'entries_field_6' )), $this->xhtml->input(array('type' => 'text', 'name' => 'OFFHOURS[]', 'value' => $offhrs, 'class' => 'entries_field_7' )) ); $body[$i] = $row; } } if($redisp == 1) { $body = array(); $name = $_SESSION['array_name']; $id = $_SESSION['array_id']; $category = $_SESSION['array_category']; $desc = $_SESSION['array_desc']; $hours = $_SESSION['array_hours']; $offhrs = $_SESSION['array_offhrs']; $dates = $_SESSION['array_dates']; $max_count = $_SESSION['max_count']; for ($i = 0; $i < $max_count; $i++) { $selected = $this->getCatName($_SESSION['UID'], $category[$i]); $row = array($this->xhtml->input(array('type' => 'text', 'id' => "datepicker$i", 'class' => 'entries_field_1', 'value' => $dates[$i], 'name' => 'DATE[]' )), $this->xhtml->input(array('type' => 'text', 'name' => 'ID[]', 'value' => "$id[$i]", 'class' => 'entries_field_2' )), $this->xhtml->input(array('type' => 'text', 'name' => 'NAME[]', 'value' => "$name[$i]", 'class' => 'entries_field_3' )), "$selected", $this->xhtml->input(array('type' => 'text', 'name' => 'DESC[]', 'value' => "$desc[$i]", 'class' => 'entries_field_5' )), $this->xhtml->input(array('type' => 'text', 'name' => 'HOURS[]', 'value' => "$hours[$i]", 'class' => 'entries_field_6' )), $this->xhtml->input(array('type' => 'text', 'name' => 'OFFHOURS[]', 'value' => "$offhrs[$i]", 'class' => 'entries_field_7' )), ); $body[$i] = $row; if(isset($_POST['CAT'])) { $_SESSION['cats'][$i] = $_POST['CAT']; } else { $_SESSION['cats'][$i] = $category[$i]; } } } $data = array_replace($head, $body); $table = $this->xhtml->table2($data, $caption, array('class' => 'add_time')); $table .= $this->xhtml->input(array('type' => 'submit', 'value' => 'Add Entries' )); $form = $this->xhtml->form(array(array($table), ), array('action' => BASE . '/addEntries.php', 'class' => 'add_time', 'onsubmit' => 'return valid_Add(this)', 'method' => 'post' ) ); $out = $form; $menu = $this->getMenu(); $msg = $this->xhtml->div("$msg", array('id' => 'error')); $out = $this->xhtml->div("$out", array('id' => 'add_entries_form')); $out = $this->xhtml->div("$msg$out", array('id' => 'main')); $out = $this->xhtml->div("$menu$out", array('id' => 'container')); $out = $this->xhtml->body("$out", array('onload' => 'startUp();')); return $out; } And here is the addEntries page that evaluates for all fields and then saves to the database if correct. <?php session_start(); include('includes/config.php'); $msg =''; $x=0; $ok_to_post = 0; $page = new STOP_Timeclock; $xhtml = new XHTML; $dbh = new Database; if (isset($_POST['NAME'])) { $name = array_filter($_POST['NAME']); $_SESSION['array_name'] = $name; } if (isset($_POST['ID'])) { $id = array_filter($_POST['ID']); $_SESSION['array_id'] = $id; } if($_SESSION['redisp'] == 0) { if (isset($_POST['CAT'])) { $category = array_filter($_POST['CAT']); $_SESSION['array_category'] = $category; } } else { $category = $_SESSION['cats']; } if (isset($_POST['DESC'])) { $desc = array_filter($_POST['DESC']); $_SESSION['array_desc'] = $desc; } if (isset($_POST['HOURS'])) { $hours = array_filter($_POST['HOURS']); $_SESSION['array_hours'] = $hours; } if (isset($_POST['OFFHOURS'])) { $offhrs = array_filter($_POST['OFFHOURS']); $_SESSION['array_offhrs'] = $offhrs; } if (isset($_POST['DATE'])) { $dates = array_filter($_POST['DATE']); $_SESSION['array_dates'] = $dates; } $count_category = count($category); //$msg .= "count of category field is " . $count_category . "<br>"; $count_desc = count($desc); //$msg .= "count of desc field is " . $count_desc . "<br>"; $count_hours = count($hours); //$msg .= "count of hours field is " . $count_hours . "<br>"; $count_dates = count($dates); //$msg .= "count of date field is " . $count_dates . "<br>"; if ($count_desc == $count_hours) { if ($count_hours == $count_dates) { $ok_to_post = 1; $max_count = $count_desc; } else { if ($count_hours > $count_dates) { $max_count = $count_desc; } else { $max_count = $count_hours; } $ok_to_post = 0; $msg .= 'You did not enter one of the required fields - Date/Category/Description/Hours are REQUIRED. <br />'; } } else { if ($count_desc > $count_hours) { $max_count = $count_desc; } else { $max_count = $count_hours; } $ok_to_post = 0; $msg .= 'You did not enter one of the required fields - Date/Category/Description/Hours are REQUIRED. <br />'; } //$msg .= "max count is set to " . $max_count . "<br>"; $_SESSION['max_count'] = $max_count; //everything is entered correctly - now process and insert into the database $rowCount = 0; $today = date("Y-m-d"); $count = 0; if ($ok_to_post == 1) { $redisp = 0; $_SESSION['redisp'] = $redisp; $dbh->beginTransaction(); $myquery = "INSERT INTO Entries (`UID`, `GID`, `Date`, `CID`, `TicketNumber`, `TicketName`, `Description`, `Hours`, `OffHours`,`Created_Date`) VALUES (:uid, :gid, :date, :cid, :num, :name, :desc, :hrs, :offhrs, now())"; $dbh->query($myquery); for ($x = 0; $x < $max_count; $x++) { $dbh->bind(':uid', $_SESSION['UID']); $dbh->bind(':gid', $_SESSION['GID']); $dbh->bind(':date', $dates[$x]); $msg .= "category[x] is set to " . $category[$x] . "<br>"; $dbh->bind(':cid', $category[$x]); if (count($id)==0) { $dbh->bind(':num', NULL); } else { $dbh->bind(':num', $id[$x]); } if (count($name)==0) { $dbh->bind(':name', NULL); } else { $dbh->bind(':name', $name[$x]); } $dbh->bind(':desc', $desc[$x]); $msg .= "desc[x] is set to " . $desc[$x] . "<br>"; $dbh->bind(':hrs', $hours[$x]); $msg .= "hour[x] is set to " . $hours[$x] . "<br>"; $dbh->bind(':offhrs', $offhrs[$x]); $result = $dbh->execute(); if (!$result) { $msg .= "Saving entry to the database failed. Please see system administrator. <br />"; continue; } $count += $dbh->rowCount(); } if ($count != $max_count) { $dbh->cancelTransaction(); $msg .= 'An error occurred and all entries could not be added. <br /> For safety, this was rolled back and no entries were added. <br /> Please see system administrator. <br />'; } else { $dbh->endTransaction(); $dbh->query("SELECT * FROM Entries WHERE UID = :uid and GID = :gid and Created_Date >= :date2"); $dbh->bind(':uid', $_SESSION['UID']); $dbh->bind(':gid', $_SESSION['GID']); $dbh->bind(':date2', $today); $rows = $dbh->resultset(); $msg .= "Returned " . count($rows) . " entries successfully written to and retrieved from the database for you today. <br>"; $tothrs = 0; foreach ($rows as $value) { $msg .= "On " . date('m-d-Y',strtotime($value['Date'])) . " for " . $value['Description'] . " - " . number_format($value['Hours'],2,'.','') . " hour(s) were recorded. <br>"; $tothrs = $tothrs + $value['Hours']; } $msg .= "Total hours entered today = " . $tothrs . "<br>"; $msg .= "<br><br>"; } $dbh = NULL; $_SESSION['MSG'] = $msg; header('Location: ' . BASE); } else { $redisp = 1; $_SESSION['redisp'] = $redisp; $_SESSION['MSG'] = $msg; header('Location: ' . BASE); } ?> I am hoping that you all might be able to assist. Thanks in advance.

SELECT user_id FROM users WHERE active = :active ORDER BY RAND() LIMIT 1 The above query works. But what if the users table is filled with thousands of users? Will this query break or be very slow? If so, what's the alternative solution to this?

I have a php program that calls a function in my class to populate a select tag for display in the page. For some reason I cannot seem to discover, it retrieves the information, it correctly creates the option tags, but then when it displays the dropdown dialog box, the very top entry is missing. I sure could use another set of eyes on this to tell me what I'm missing as I know it has to be something simple. Here is the php page code that calls the function: if ($redisp==1) { $table_ex_entries .= "<table><tr>"; for ($x=0; $x<10; $x++) { //get the categories list with the selected one highlighted in the list $selected = $page->getGroupsForEntriesForm3($_SESSION['UID'],$entry_cat[$x]); //get the value for the off hours radio button if (isset($entry_offhrs[$x]) && $entry_offhrs[$x]=='y') { $set_checked_y = "checked='checked'"; $set_checked_n = ""; } else { if (isset($entry_offhrs[$x]) && $entry_offhrs[$x]=='n') { $set_checked_n = "checked='checked'"; $set_checked_y = ""; } else { $set_checked_n = "checked='checked'"; $set_checked_y = ""; } } $table_ex_entries .= "<tr>"; $table_ex_entries .= "<td><input size='12' name='date" . $x . "' type='text' value='" . $entry_date[$x] . "' id='datetimepicker" . $x . "'></td>"; $table_ex_entries .= "<td><input size='12' name='id" . $x . "' type='text' value='" . $entry_id[$x] . "'></td>"; $table_ex_entries .= "<td><input size='18' name='name" . $x . "' type='text' value='" . $entry_name[$x] . "'></td>"; $table_ex_entries .= "<td><select width='250' name='cat" . $x . "' value='" . $selected . "</select></td>"; $table_ex_entries .= "<td><input size='43' name='desc" . $x . "' type='text' value='" . $entry_desc[$x] . "'></td>"; $table_ex_entries .= "<td><input size='6' name='hrs" . $x . "' type='text' value='" . $entry_hrs[$x] . "'></td>"; $table_ex_entries .= "<td>Yes<input name='offhrs" . $x . "' type='radio' value='y' " . $set_checked_y . "> No <input name='offhrs" . $x . "' type='radio' value='n' " . $set_checked_n . "></td>"; $table_ex_entries .= "</tr>"; } $table_ex_entries .= "</table>"; } Here is the div in the html body (it displays fine as I have some javascript that determines which div to display based on the $redisp==1 show in the above code snippet) <div id="re_entry" style="display:none"> <p align="center"> *<b>Date/Category/Description/Hours are REQUIRED fields</b> <br>Please correct your entries and click Add Entries again to proceed. <br> </p> <?php echo $table_header; ?> <?php echo $table_ex_entries; ?> </div> And here is the function that it calls to create the option tags for the select tag in the first set of code building the html table. Oh and I do have categories preferences so it is retrieving just my list not the whole list in this function. public function getGroupsForEntriesForm3($uid,$category) { $out = ''; $cats = array(); $cat_id = array(); $catname = array(); $dbh = $this->rdb; //check to see if the user has set preferences for their list of categories $dbh->query("SELECT Categories FROM Users WHERE ID = :id"); $dbh->bind(':id', $uid); $row = $dbh->single(); $user_cats = (isset($row['Categories']) ? $row['Categories'] : ''); if ($user_cats == '') { //no special preference for categories so go get them all after getting the user's group ID $dbh->query("SELECT GID FROM Users WHERE ID = :id"); $dbh->bind(':id', $uid); $row = $dbh->single(); $gid = $row['GID']; $cats = $this->getCategories($gid); } else { //user has preference for categories so only go get the ones they have in their preference list to create the select options $dbh->query("SELECT ID, Name FROM Categories WHERE ID IN ($user_cats) ORDER BY Name"); $rows = $dbh->resultset(); $xx=0; foreach ($rows as $row) { $catname[$xx] = $row['Name']; $cat_id[$xx] = $row['ID']; $xx++; } } $dbh = NULL; $select_opt = ""; for ($xx=0; $xx<count($rows); $xx++) { //echo "category ID inside the function is set to " . $category . "<br>"; //echo "catname while while creating the select options set to " . $catname[$xx] . "<br>"; if ($category == $cat_id[$xx]) { //echo "match category is " . $category . " and cat_id[xx] is " . $cat_id[$xx] . " and catname[xx] is set to " . $catname[$xx] . " and xx is set to " . $xx . "<br>"; $select_opt .= "<option selected value='" . $cat_id[$xx] . "'>" . $catname[$xx] . "</option>"; } else { //echo "category is " . $category . " and cat_id[xx] is " . $cat_id[$xx] . " and catname[xx] is set to " . $catname[$xx] . " and xx is set to " . $xx . "<br>"; $select_opt .= "<option value='" . $cat_id[$xx] . "'>" . $catname[$xx] . "</option>"; } } return $select_opt; } Thanks in advance! Ches