Jump to content

Steven

Members
  • Posts

    150
  • Joined

  • Last visited

Everything posted by Steven

  1. So I have a long query and I'm trying to format the date (the "datein" column is date type). Here's my query: "SELECT j.*, c.*, DATE_FORMAT(datein, '%M %d, %Y') AS datein FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = $id" It responds with this error: Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement in C:xampphtdocsmos-logviewjob.php on line 29 So I figured what was happening, was "datein" was being pulled twice, conflicting with my prepared statement. So I tried changing my query so all columns from the jobs table were SELECTED before the date_format except for "datein". Well it still gave me the error. I've been rummaging the mysql manual and stackoverflow, but haven't found anything that helps me understand what's going on.
  2. Gotchya, thanks! It's actually a date type in the database. Should that be an 's' then, and not an 'i'? --- EDIT ------ Yes, it appears as though it does need to be a string. I was confused for a while, because I wasn't ever noticing in the php manual anywhere saying dates need to be strings.
  3. Alright, well I don't understand it, but I changed $r = @mysqli_query ($con, $q); // run the query if ($r) { // if $r ran with no errors echo "<h2>Thank you!</h2>"; echo "<p>The job has been successfully recorded.</p>"; } else { // if $r ran with errors echo "<h2>Oh, bother...</h2>"; echo "<p>Something goofed. Sorry about that.</p>"; // Debugging message echo "<p>".mysqli_error($con)."</p>"; echo "<p>Query: ".$q."</p>"; } // end of $r IF to if ($q) { // if $r ran with no errors echo "<h2>Thank you!</h2>"; echo "<p>The job has been successfully recorded.</p>"; } else { // if $r ran with errors echo "<h2>Oh, bother...</h2>"; echo "<p>Something goofed. Sorry about that.</p>"; // Debugging message echo "<p>".mysqli_error($con)."</p>"; echo "<p>Query: ".$q."</p>"; } // end of $r IF And it's running fine, without those SQL query errors. Thanks! However, it's still not registering the dates, and I still can't tell what's going on there.
  4. It looked out of order to me, but the manual lays it out this same way: http://us.php.net/manual/en/mysqli-stmt.bind-param.php Could you explain how $r is suppressing errors? As far as I know, it is what allows reporting these errors: if ($r) { // if $r ran with no errors echo "<h2>Thank you!</h2>"; echo "<p>The job has been successfully recorded.</p>"; } else { // if $r ran with errors echo "<h2>Oh, bother...</h2>"; echo "<p>Something goofed. Sorry about that.</p>"; // Debugging message echo "<p>".mysqli_error($con)."</p>"; echo "<p>Query: ".$q."</p>"; } // end of $r IF I'm not arguing it at all, I just don't really know any better and want to understand what you are saying better.
  5. I changed the form's action from insertJob.php to the current page the form is on (I overlooked that in the example from the book). Well, it's sort of working now. I can see the error reporting now, which is nice. It's successfully telling me when and where errors are happening. But, it's having trouble with the sql query. It's throwing this error at me: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?)' at line 1Query: INSERT INTO jobs (clientid, datein, description) VALUES (?,?,?) Here is the section with my prepared stmt: // If it's all good... if (empty($errors)) { // ...send form to the database // Make the query $q = 'INSERT INTO jobs (clientid, datein, description) VALUES (?,?,?)'; // Prepare the statement $stmt = mysqli_prepare($con, $q); // Bind the variables mysqli_stmt_bind_param($stmt, 'iis', $clientid, $datein, $description); // Asign the values to variables $clientid = $_POST['clientid']; $datein = $_POST['datein']; $description = $_POST['description']; // Execute mysqli_stmt_execute($stmt); // Print a message based on the result if (mysqli_stmt_affected_rows($stmt) == 1) { echo '<p>Great! A new job has been added.</p>'; } else { echo '<p>Sadly, the query could not be exeucted.</p>'; echo '<p>'.mysqli_stmt_error($stmt).'</p>'; } // Close the statement mysqli_stmt_close($stmt); $r = @mysqli_query ($con, $q); // run the query if ($r) { // if $r ran with no errors echo "<h2>Thank you!</h2>"; echo "<p>The job has been successfully recorded.</p>"; } else { // if $r ran with errors echo "<h2>Oh, bother...</h2>"; echo "<p>Something goofed. Sorry about that.</p>"; // Debugging message echo "<p>".mysqli_error($con)."</p>"; echo "<p>Query: ".$q."</p>"; } // end of $r IF mysqli_close($con); // close the db connection } else { // Report the errors echo "<h2>Error!</h2> <p>The following error(s) occurred:<br>"; foreach ($errors as $msg) { // Print each error echo " - $msg<br> "; } echo "</p><p>Please try again.</p><p><br></p>"; } // end of (empty($errors)) IF There is another funny error showing up. When the page shows these errors after having tried to submit an entry, my clientid dropbox disappears and shows this error in its place: Warning: mysqli_prepare(): Couldn't fetch mysqli in C:xampphtdocsmos-logformJobsValidate.php on line 165 And here's the relevant PHP for that: <?php // prepare statement if ($stmt = mysqli_prepare($con, "SELECT * FROM clients")) { mysqli_stmt_execute($stmt); // bind variables to prepared statement // list all* columns in order of tables selected! mysqli_stmt_bind_result($stmt, $id, $name); // fetch values echo "<select name='clientid'>"; while (mysqli_stmt_fetch($stmt)) { echo "<option value='".$id."'>".$name."</option>"; } echo "</select>"; } ?> And... another weird thing. This submission actually made its way to the database, despite the error message. However, once it got there, the date is empty, even though I picked a date with the datepicker. ---- EDIT ------- Okay, well I fixed the issue where the dropbox disappeared. Earlier in the code, where I pasted in the $stmt blocks from another page, there was a call to close the database connection. I got rid of that, and now it is working. I'm still completely stumped as to why I'm getting query errors when the query is still being added, and why the date field is being read as "0000-00-00" when I enter a date using the jquery datepicker plugin.
  6. I tried sending a job with "date" and "description" left empty. There is only one other field, "client". This client field is a dropdown, and can't very-well be left blank. Is my code written in such a way that causes it to send the query to the database even if just one field is filled in? It isn't even showing me the error reports for the two fields left empty. Also, I'm pretty sure I have error reporting turned on.
  7. Thanks, I'll chew on that link for a while, and likely come back with more questions.
  8. Okay, here is a bigger question... I got everything wrote out, sent a test submission with a few empty fields, and it still sent it to the database without error. Not what I want happening. I can't see anything wrong with my code -- there certainly IS something wrong, but my newby eyes can't see it. What's going on with this code: <div id="container"> <!-- Include Navigation --> <?php include ('includes/header.php'); ?> <!-- Include Navigation --> <?php include ('includes/navigation.php'); ?> <h2>Add a new job (Validation)</h2><?php// Check for form submissionif ($_SERVER['REQUEST_METHOD'] == 'POST') { // Initialize an error array $errors = array(); // Check for a client entry if (empty($_POST['clientid'])) { $errors[] = 'You forgot to enter a client'; } else { $clientid = trim($_POST['clientid']); } // Check for a date entry if (empty($_POST['datein'])) { $errors[] = 'You forgot to enter the date'; } else { $datein = trim($_POST['datein']); } // Check for a description entry if (empty($_POST['description'])) { $errors[] = 'You forgot to enter a description'; } else { $description = trim($_POST['description']); } // If it's all good... if (empty($errors)) { // ...send form to the database $q = "INSERT INTO jobs (clientid, datein, description) VALUES ($clientid, $datein, $description)"; $r = @mysqli_query ($con, $q); // run the query if ($r) { // if $r ran with no errors echo "<h2>Thank you!</h2>"; echo "<p>The job has been successfully recorded.</p>"; } else { // if $r ran with errors echo "<h2>Oh, bother...</h2>"; echo "<p>Something goofed. Sorry about that.</p>"; // Debugging message echo "<p>".mysqli_error($con)."</p>"; echo "<p>Query: ".$q."</p>"; } // end of $r IF mysqli_close($con); // close the db connection } else { // Report the errors echo "<h2>Error!</h2> <p>The following error(s) occurred:<br>"; foreach ($errors as $msg) { // Print each error echo " - $msg<br> "; } echo "</p><p>Please try again.</p><p><br></p>"; } // end of (empty($errors)) IF} // end of the main Submit conditional?><!-- FORM!! --><div class="formContainer"> <form action="insertJobs.php" method="post"> <p> <label>Client:</label> <?php // prepare statement if ($stmt = mysqli_prepare($con, "SELECT * FROM clients")) { mysqli_stmt_execute($stmt); // bind variables to prepared statement // list all* columns in order of tables selected! mysqli_stmt_bind_result($stmt, $id, $name); // fetch values echo "<select name='clientid'>"; while (mysqli_stmt_fetch($stmt)) { echo "<option value='".$id."'>".$name."</option>"; } echo "</select>"; } ?> <br> <label>Date Received:</label> <input type="date" name="datein" class="focus-glow datepicker job--dateinInput" size="23"> <span class="job--dateinHint">Date placed</span><br> <label>Description:</label> <textarea type="text" maxlength="600" name="description" class="focus-glow" size="28"></textarea> <p> <input type="submit" label="Submit"> <p> </form>
  9. I went through my application and implemented prepared statements where needed, and all is well now. What I'm up to now is implementing form validation. I'm doing a little something like this: // Check for form submissionif ($_SERVER['REQUEST_METHOD'] == 'POST') { // Initialize an error array $errors = array (); // Check for a date entry if (empty($_POST['datein'])) { $errors[] = 'You forgot to enter a date'; } else { $datein = trim($_POST['datein']); }// rest of the stuff...} After a stressful morning I've come to understand what's going on with that code example a bit (I'm getting this method from the Larry Ullman book I got recently). My question is, with this validation method, where each of the posts (at least the ones that are validated) are turned into variables, does that get rid of the need of prepared statements when it comes time to execute the SQL query? Do I need prepared statements in this case?
  10. Steven

    Div positioning

    The image you are trying to put in the banner div, would it work to use it as a background image? So, instead of using <img src="my-picture.jpg"> you would have: #banner { background: url(my-picture.jpg) center; }
  11. Alright, with the help of those links, I got it working fairly well: <?php echo "<table>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Client</th>"; echo "</tr>"; // prepare statement if ($stmt = mysqli_prepare($con, "SELECT * FROM clients ORDER BY id")) { mysqli_stmt_execute($stmt); // bind variables to prepared statement mysqli_stmt_bind_result($stmt, $id, $name); // fetch values while (mysqli_stmt_fetch($stmt)) { echo "<tr>"; echo "<td>$id</td>"; echo "<td>$name</td>"; echo "</tr>"; echo "</table>"; } // close statement mysqli_stmt_close($stmt); }?> Only problem is, it displays only the first result inside the table. The rest of them look as though they are outside the table. I tried browsing StackOverflow and came across this one. But the one answer makes an odd suggestion: while ($row = mysqli_fetch_array($stmt)) { echo "<tr>"; echo "<td>" . $row["aid"] . "</td>"; echo "<td>" . $row["aname"] . "</td>"; echo "</tr>";} To me, that seems to defeat the purpose of binding the results... ---- EDIT ------- Okay, I made a newby mistake. Read up on the "while" statement, and realized I had included echo "</table>"; which was causing the table to close after each iteration. I moved that outside of the while loop, and it's working.
  12. Steven

    css positioning

    This article by Nicolas Gallagher has helped me out a number of times. It's an updated, simpler version of the clearfix issue you've been dealing with.
  13. Oh, sure. That makes sense. I'm doing some reading on the links you gave me, and looked into bind_param and bind_results a bit more. Now, correct me if I'm wrong, but it looks like bind_param is used for sending input to the database, but bind_results is for getting results from the database to print on your page. That must be part of my problem as well, as I've been trying to get bind_param to work when I'm trying to fetch results from my query.
  14. This is what I did: <label>Client:</label> <?php $result = mysqli_query($con,"SELECT * FROM clients"); echo "<select name='clientid'>"; while ($row = mysqli_fetch_array($result)) { echo "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>"; } echo "</select>"; ?>
  15. I tried this: $q = "SELECT * FROM clients WHERE name=?"; $stmt = mysqli_prepare($con,$q); mysqli_stmt_bind_param($stmt, 's', $row['name']); mysqli_stmt_execute($stmt); echo "<tr>"; echo "<td>".$row['name']."</td>"; echo "</tr>"; echo "</table>"; But it isn't returning anything. It isn't showing errors, displays the page but no info from the table.
  16. .content { -webkit-box-sizing: border-box; -moz-box-sizing: border-box; box-sizing: border-box;} That fixes it. As Ingolme said, padding adds to the dimensions of the element. A div with 100px width and 20px padding will result in an actual width of 120px. Chris Coyier has a great article on this, as well as Paul Irish. This might not work out for every situation, though, as only IE 8+ supports it (naturally Firefox, Chrome, Opera and Safari have good support).
  17. Are prepared statements only used in conditional SQL queries? For example, say I have a simple query: SELECT * FROM clients, does that need prepared statements? I tried to give it one, but it's erring out on me. Here's my code: echo "<table>"; echo "<tr>"; echo "<th>Client</th>"; echo "</tr>"; $q = "SELECT * FROM clients WHERE name=?"; $stmt = mysqli_prepare($con,$q); mysqli_stmt_bind_param($stmt, 's', $name); $name = $row['name']; mysqli_stmt_execute($stmt); echo "<tr>"; echo "<td>"."$name"."</td>"; echo "</tr>"; echo "</table>"; The "$name = $row['name'];" doesn't seem right to me, but how else would I grab the actual value for $name? Or, as I originally asked, are prepared statements only used on conditional statements? That'd seem a little odd if that were the case.
  18. Oh, it looks like jQuery Autocomplete does have that ability: http://jqueryui.com/autocomplete/#combobox And I agree, I should just stop what I'm doing and implement what you've suggested. I'll read up on that now, thanks.
  19. I have a follow-up question. on the jobsform page, we switched it to a dropdown list so we could have the client id available. Previously, while still using a text field to fill in the client name, I used jQuery Autocomplete to suggest client names from the client table as the user was typing. Now, as more users get added, a dropdown method will become unruly. Is there a way to use the jQuery Autocomplete method to perform the same way?
  20. I think I fixed it. I bought "PHP and MySQL" by Larry Ullman and a similar O'Reilly book on Amazon and they came yesterday. Larry Ullman's book is paying off already. I snuck ahead and used a snippet of his and came up with this: $result = mysqli_query($con, "SELECT j.*, c.* FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = $id"); I altered this a little bit for the viewclients page, and it worked there as well. No duplicates. I also realized, at least on the viewclients page, I was getting the client name constantly duplicated, because I had the name included in the while loop with all the jobs. So, I did this: $id = $_GET['clientid']; $name = mysqli_query($con, "SELECT * FROM clients WHERE id=$id"); while($row = mysqli_fetch_array($name)) { echo '<h2 class="h2--viewClient">'.$row['name'].'</h2>'; } $result = mysqli_query($con, "SELECT j.*, c.* FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE c.id = $id"); while($row = mysqli_fetch_array($result)) { echo '<p>Job ID: '.$row['jobid'].'</p>'; echo '<ul>'; echo '<li>Date Ordered: '.$row['datein'].'</li>'; echo '<li>'.$row['description'].'</li>'; echo '</ul>'; } It's working great, but is that the correct way to do that? Nevermind normalization and security -- for now. I'll be revisiting all this once I cover that chapter in Ullman's book and have a firmer understanding. Thanks for all the help so far.
  21. The Dude abides!

  22. I looked at the indices in PMA and it looked like there was some duplicate key on jobid_2. I removed that, tried again, and now it's able to send more jobs. I wonder how that was interfering?
  23. I think it is for the jobs table, and clientid column. the primary key for the jobs table is clientid. Looking at it, I'm not sure why I did that, jobid should be the primary key, shouldn't it? In any case, clientid is the primary key for jobs table.
  24. I think I fixed it... I changed this snippet in resultsJobs.php: $result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients"); to...: $result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients WHERE clientid=id"); Now, it appears as though it is doing what I want, but is that the correct way to do it? Just want to make sure I'm doing this right --- EDIT -------- I take that back. I just attempted to submit a second job for "client 02", but it spits this back at me: "Error: Duplicate entry '2' for key 'PRIMARY'" In the clients table, the id is unique, and should not be duplicated. But in the jobs table the clientid should be allowed to duplicated, as clients have multiple job orders... Is this possible with foreign keys? =
  25. Thanks guys, I'm getting close. But I'm getting a duplication bug here. Here's my formJobs.php: <?php$result = mysqli_query($con,"SELECT * FROM clients");echo "<select name='clientid'>";while ($row = mysqli_fetch_array($result)) { echo "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>";}echo "</select>";?> Here's insertJobs.php: $sql="INSERT INTO jobs (clientid, datein, description) VALUES ('$_POST[clientid]','$_POST[datein]','$_POST[description]')"; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo "1 record added"; And here's resultsJobs.php: echo "<table><tr><th>Job ID</th><th>Client ID</th><th>Client</th><th>Date</th><th>Description</th></tr>";$result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients");while($row = mysqli_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['jobid'] . "</td>"; echo "<td>" . $row['clientid'] "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "<td>" . $row['datein'] . "</td>"; echo "<td>" . $row['description'] . "</td>"; echo "</tr>";}echo "</table>"; I'm not getting any error messages, but I'm missing something. When I view my results page, it shows me two rows in my table I told it to feed the results to, it looks like this: -------------------------------------------------------------------------| Job ID | Client ID | Client | Date | Description |-------------------------------------------------------------------------| 12 | 2 | "client.02" | 2014/03/20 | abc || 12 | 2 | "client.01" | 2014/03/20 | abc |------------------------------------------------------------------------- Now, it's correctly reporting that the job I just submitted "job #12" belongs to "client #2", but I'm having trouble figuring out why it is duplicating the job for every client. It says "job #12" also belongs to client #1, and is reporting client #1 as having id #2...
×
×
  • Create New...