Jump to content

Psycho

Moderators
 View Profile  See their activity

  • Posts

    12,146

  • Joined

  • Last visited

  • Days Won

    127

 Content Type 

Everything posted by Psycho

  1. Psycho
    Because the variables you are using to add the values to the query are either not defined or are defined incorrectly. THe other errors you reported clearly showed that.
  2. Psycho
    You did NOT use backticks => `````````. You used regular single ticks => '''''''' fastsol even told you exactly where on the keyboard the character was! Also, the variables you are using to insert data are not defined!
  3. Psycho
    There's nothing wrong with using backticks for the column names as used above. If any column name was a reserved word they would be required.
  4. Psycho
    I think you are taking the wrong approach as this will require three queries instead of one, but here, give this a tryu <?php //Make DB connection include('connection.php'); //Set the ID $the_id = (isset($_GET['id']) && ctype_digit($_GET['id'])) ? $_GET['id'] : 0; //Vars to hold the dynamic output $output = ''; $pagination = ''; //Run query to get the selected record data $query = "SELECT the_title, the_subject, summary, view, Image FROM the-table WHERE the_id = $the_id"; $result = mysqli_query($con, $query) or die(mysqli_error($con)); $record = mysqli_fetch_assoc($result); //Check that there was a match if(!$record) { $output .= 'No record found'; $pagination = ''; } else { //Create record output $output .= "<div class='containerDetail'>\n"; $output .= " <h1>title {$record['the_title']}</h1>\n"; $output .= " <ul>\n"; $output .= " <li>subject: {$record['the_subject']}</li>\n"; $output .= " <li>summary: {$record['summary']}</li>\n"; if(!empty($record['view'])) { $output .= " <li><a href='{$record['view']}'>View</a></li>\n"; } $output .= " </ul>\n"; $output .= "</div>\n"; $output .= "<img src='{$record['Image']}' /> \n"; //Run query to get the prev record id $query = "SELECT MAX(the_id) FROM the-table WHERE the_id < $the_id"; $result = mysqli_query($con, $query) or die(mysqli_error($con)); $prevID = mysqli_fetch_assoc($result); //Run query to get the next record id $query = "SELECT MIN(the_id) FROM the-table WHERE the_id > $the_id"; $result = mysqli_query($con, $query) or die(mysqli_error($con)); $nextID = mysqli_fetch_assoc($result); //Create the pagination output $pagination .= "<div class='pagination'>\n"; $pagination .= " <div>\n"; $pagination .= " <div id='paglinks'>\n"; $pagination .= ($prevID) ? "<a href='./thepage.php?id={$prevID}'>« Previous </a>" : "« Previous"; $pagination .= " | "; $pagination .= ($nextID) ? "<a href='./thepage.php?id={$nextID}'>Next »</a>" : "Next »"; $pagination .= " </div>\n"; $pagination .= " </div>\n"; $pagination .= "</div>\n"; } ?> <?php include("head.php"); ?> <body class=""> <?php include("navigation.php"); ?> <section class="container"> <?php echo $output; ?> <?php echo $pagination; ?> </section> </body> </html>
  5. Psycho
    Try adding a "type" to the add row button as <button onclick="addRow()" type="button">Add Row</button>
  6. Psycho
    Can you provide the "output" of the script - i.e. the HTML source that is generated? I don' have your database in order to run the script. Since this is a JS problem, only the HTML is needed.
  7. Psycho
    Why would that button cause the form to be submitted? It isn't created as a submit button - so it wouldn't submit the form unless you were to apply JS to the button to force it to submit the form
  8. Psycho
    How can I provide that? You should already have code to display the data from the "selected" record. I've given you the solution, but apparently there is some confusion on that solution. I think you are to wrapped up in what you think you need to be doing that you are not understanding what I have provided. The solution is to implement the "normal" pagination process that allows the use to navigate from 'page' to 'page'. In this case the number of records for each page will be 1 (as opposed to 20 or some other set number). You do NOT need to determine the ID of the prev or next pages. That is only making this harder than it needs to be. EDIT: The ONLY reason you would need to use a different process is if you need to display "data" regarding the Prev & Next records on the current page. Please provide the code to output the complete page and I'll see if I can rewrite as needed.
  9. Psycho
    I guess you didn't read my first post. You are making this more difficult than it needs to be. Just follow the "normal" pagination logic and make the page size 1. You do not need to know the ID of the next record to make this work. If you know there is another "page" of data (in this case at least a single record), then pass the next page number to the script. Then it doesn't matter if there is a gap of one or multiple numbers between record IDs - it just works! To reiterate: You should be passing the PAGE number - not the record ID. It will work - I promise. Also, I created the pagination controls as a variable for a reason. It gets unwieldy to manage code when you are going in and out of PHP/HTML repeatedly. Put all of your PHP code at the top of your script and create variables to hold the respective output. Then put the HTML at the bottom of the script just using echo's to output the dynamical generated content.
  10. Psycho
    There is not going to be an easy way to just drop in some code to make this happen. Before you even start you need to fully understand your database schema. Otherwise, you run the risk of creating orphaned records that could be benign or could create real problems. For example, if you were to delete a client - that client may have placed order or had other records associated with them. What should happen to that associated data when the client is "deleted". If you delete the client and the associated data, then you lose all history of that associated data as well. For example, if you were to run an order history report it would be wrong. Also, if any inventory is based on order history the inventory would be wrong. Or, if you delete the client and not the associated data, how would it be used? If you ran an order history report you could still have a problem where the orders are not reported depending on how the query is built. Or, if it is included, what should display in those reports where the client is included in the output. Way, way too many issues you need to understand and plan for before creating any processes for deleting records. You will likely find that some records can be permanently deleted, while some can be "soft" deleted and other cannot be deleted at all. Here is an example of one possible scenario: Let's say you have a value for "Region" that is applied to clients. If any clients are assigned to a region AND region is a required value, then you can allow a region to be deleted if no clients are assigned to it. Or, during the delete operation, you could give the option to reassign any clients to a new region. If region is not required, then you can probably just allow any to be deleted without any checks - but you need to update the existing clients that reference the region to an appropriate Nu// or Default value.
  11. Psycho
    Here is a rough outline of how you could redo the code. This is not tested and written "off the cuff" so there may be a few syntax errors. But, it should point you in the right direction. <?php $recordsPerPage = 1; include('connection.php'); //Get the current page to display $currentPage = isset($_GET['page']) ? intval($_GET['page']) : 1; //Calculate the limit start value $limitStart = ($currentPage-1) * $recordsPerPage; //Get the current page records ## NOTE the SQL_CALC_FOUND_ROWS allows you to use a query to get the ## current page records and calculate the total records (w/o the limit) $query = "SELECT SQL_CALC_FOUND_ROWS field1, field2 FROM thetable ORDER BY field1 LIMIT {$limitStart}, {$recordsPerPage}"; $result = mysqli_query($con, $query); //Run second query to get the total records calculated from 1st query $query = "SELECT FOUND_ROWS()"; $result_count = mysqli_query($con, $query); $rowResult = mysqli_fetch_array($result_count); $totalRecords = $rowResult[0]; //Calculate total pages $totalPages = ceil($totalRecords / $recordsPerPage); //Create pagination controls $paginationControls = ''; //Previous page if($currentPage > 1) { $prevPage = $page - 1; $paginationControls .= "<a href=\"./thepage.php?page={$prevPage}\">« Previous </a>"; } else { $paginationControls .= "« Previous"; } //Next page if($currentPage < $totalPages) { $nextPage = $page + 1; $paginationControls .= "<a href=\"./thepage.php?page{$nextPage}\">Next »</a>"; } else { $paginationControls .= "Next »";; } //ADD CODE TO DISPLAY THE RECORD(S) mysqli_close($con); ?>
  12. Psycho
    Lot's of things wrong in that code: 1. NEVER use data directly from the user (e.g. $_POST, $_GET, etc.) in a query. You are opening yourself up to SQL injection. Either verify the data is 'valid' or, better yet, use prepared statements. 2. Don't use mysql_ functions - they are deprecated. You are mixing those and mysqli_ functions. 3. Don't use "*" in your SELECT query unless you really need all the fields. Plus, don't query all the rows and then use num_rows. Instead run a query to return the number of rows. 4. That code would not work as you have it. You are running a query to get the number of rows, and then using the result as a new query. ??? Since each "page" is a single record, you can change your logic to use a more traditional pagination approach and just limit the page size to one record. The prev/next links do NOT need the ID of the records. That way you can change the records per page at will.
  13. Psycho
    I don't see anything in your code that is trying to clone the select list. You should NOT hard-code the fields to be copied/cloned into the JavaScript code. Instead you should put the 'group' of fields to be copied into a DIV or other container and then dynamically copy the fields in that container. That way, when you make changes to the fields in the future, you only have to change the fields in the initial form (i.e. PHP) and you don't have to change the JavaScript. Here is a very "rough" example: http://jsfiddle.net/sEtmN/3/
  14. Psycho
    Where's the code from the file state.php? Please star out the password when you do. But, to me it looks like there isn't a connection made. If you post the code we can add some debugging logic to hopefully provide a better idea of the problem.
  15. Psycho
    It would be helpful to see the logic you are using to get a page of records and output it to the page (including the queries). It could be you are running queries in loops, the query you are using is inefficient, or you need to add indexes on specific fields used in the query. (or a combination of the three).
  16. Psycho
    As indicated by chriscloyd's code - do not duplicated code. You apparently only want to change the content of the page and there is no need to replicate the creation of the HTML, HEAD and other tags. Use php to determine the dynamic content and "plug it in" to the template of the page. I would make one change to chriscloyd's code and determine the content BEFORE the output is started. As for a blank page, did you view the HTML source of the page? I'm not very good with flash and don't know if the code you have would produce and output. I modified the above code to add some debugging logic so you can verify if the page is getting executed correctly. <?php $ouput = "Please login to access this page!"; $debug = "Condition is false"; if($user->data['is_registered']) { $ouput = '<script type="text/javascript"> var multiplayer = new SWFObject("player.swf?xml=config.xml&player_skin=skin.swf", "multiVideoPlayer", "361", "283", "7", "#FFFFFF", true); multiplayer.write("flashcontent"); </script>'; $debug = "Condition is true"; } ?> <html> <head> <script type="text/javascript" src="swfobject.js"></script> </head> <body> <?php echo $output; ?> <br> <?php echo $debug; ?> </body> </html>
  17. Psycho
    I made some assumptions and made this so it would be very flexible $functions = array('SUM', 'MAX', 'MIN', 'AVG'); $patterns = array(); $replacemetns = array(); foreach($functions as $func) { $patterns[] = "#({$func})\(([^\)]*)\) as (\"){0,1}([^\"]*)(\"){0,1}#"; $replacements[] = "\\1(\\2) over() \\3\\4\\5"; } $output = preg_replace($patterns, $replacements, $input); If this is your input SELECT field1, field2, field3, SUM('field3') as 'field3sum', --Single quotes MAX("field4") as "field4max", --Double quote AVG(field5) as field5avg --No quotes FROM table_name This will be the output SELECT field1, field2, field3, SUM('field3') over() 'field3sum', --Single quotes MAX("field4") over() "field4max", --Double quote AVG(field5) over() field5avg --No quotes FROM table_name
  18. Psycho
    Are "foo" and "bar" always going to be "foo" and "bar" or can they be variable text values? EDIT: Also, you say the value inside the aggregate function may or may not have quotes. Can it use single quotes as well? And what about the quotes around "Bar" can that use single or double quotes - or no quotes?
  19. Psycho
    Yeah, it's kind of hard for me to test a query without the requisite DB. I don't use CASE statements all that often and I goofed on the format - it should just be "END" not "END CASE". Although that's not what the manual says. Also, I found it could be simplified a little: //Define the WHEN conditions for WHERE clause CASE statement $cutoff_scores = array(25=>7,50=>14,75=>28,90=>40); $WHENs = ''; foreach ($cutoff_scores as $score => $rebox_interval) { $WHENs .= "WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY) THEN (right_count / (right_count + wrong_count)) <= '$score'1\n"; } //Create the query $query = "UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() WHERE box < 7 AND CASE {$WHENs} ELSE 0 END"; $result= mysqli_query($link, $query); Resulting query UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() WHERE box < 7 AND CASE WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN (right_count / (right_count + wrong_count)) <= '25'1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY) THEN (right_count / (right_count + wrong_count)) <= '50'1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY) THEN (right_count / (right_count + wrong_count)) <= '75'1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY) THEN (right_count / (right_count + wrong_count)) <= '90'1 ELSE 0 END CASE If a WHEN condition is met it will return the result of the THEN condition.
  20. Psycho
    OK, after a little research, I see you can use a CASE statement in the WHERE clause. I don't know if this is any more efficient or not. But, I think it makes the logic and the resulting query a little easier to understand //Define the WHEN conditions for WHERE clause CASE statement $cutoff_scores = array(25=>7,50=>14,75=>28,90=>40); $WHEN = ''; foreach ($cutoff_scores as $score => $rebox_interval) { $WHEN .= "WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY) AND (right_count / (right_count + wrong_count)) <= '$score' THEN 1\n"; } //Create the query $query = "UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() WHERE box < 7 AND CASE {$WHEN} ELSE 0 END CASE"; $result= mysqli_query($link, $query); Resulting Query UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() WHERE box < 7 AND CASE WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND (right_count / (right_count + wrong_count)) <= '25' THEN 1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND (right_count / (right_count + wrong_count)) <= '50' THEN 1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY) AND (right_count / (right_count + wrong_count)) <= '75' THEN 1 WHEN last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY) AND (right_count / (right_count + wrong_count)) <= '90' THEN 1 ELSE 0 END CASE
  21. Psycho
    OK, I thought you might need a SWITCH statement in the query to handle all the updates in one query. But, after further review I see that the UPDATES are all the same. The differences are with the WHERE conditions for determining which records to update. That males it a little simpler since you just need to build a combined WHERE condition. It looks complicated, but isn' that hard. You have to know what the final query should look like. Then create the code to dynamically create it. //Define the cuttoff conditions for WHERE clause $cutoff_scores = array(25=>7,50=>14,75=>28,90=>40); $CUTOFFS = array(); foreach ($cutoff_scores as $score => $rebox_interval) { $CUTOFFS[] = "(last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL {$rebox_interval} DAY) AND (right_count / (right_count + wrong_count)) <= '$score')"; } //Define the WHERE clause $WHERE = "WHERE box < 7 AND (\n" . implode("\n OR ", $CUTOFFS) . "\n)"; $query = "UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() $WHERE"; $result= mysqli_query($link, $query); The resulting query would look like this UPDATE leitner_vcard_boxes SET box = (box + 1), last_reboxed_date = CURDATE() WHERE box < 7 AND ( ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND (right_count / (right_count + wrong_count)) <= '25') OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 14 DAY) AND (right_count / (right_count + wrong_count)) <= '50') OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 28 DAY) AND (right_count / (right_count + wrong_count)) <= '75') OR ( last_reboxed_date < DATE_SUB(CURDATE(), INTERVAL 40 DAY) AND (right_count / (right_count + wrong_count)) <= '90') ) Also, you should see from the above the answer to your original question. Since you don't have a Right_Percent column in the database and calculate it dynamically (as you should) you just need to use that calculation in the WHERE condition as well. No need for a subquery.
  22. Psycho
    Hmm . . . that query is invalid. Are you modifying it before posting here? EDIT: I am seeing several things to be addressed: 1. You are trying to execute the variable $interval as the Query! WTH?! Not even sure what $interval should be used for as it doesn't appear in the $query. So, why is the loop even needed? 2. There are two values being set but there is no comma between them (this makes the query invalid) 3. You are creating a date string in PHP to be used in the query. That's unnecessary. You can either just use CURDATE() in the query OR if you want that date always updated whenever the record is updated, change the field to a timestamp with the option to auto-update. Then you don't even need to add the date field to your query. It would just be updated to the current timestamp whenever there is a change. 4. Same goes for $cutoff_date. It can be set directly in the query 5. You are correct - you do not need to run multiple queries. But, because there is an obvious problem with how the "values" of the array are not used in the query, I can't provide a solution. Edit #2: Scratch the comments regarding the problem with not knowing how you are wanting to use the value of the array in the query. I see it is used to calculate the cutoff date.
  23. Psycho
    Yes, it's absolutely possible. But,you are talking about how to "display" the data. You would just query the data and have logic (i.e. code) for determining the output to display as you wish. SO, this is really a PHP question (assuming you are using PHP). Please show the code you have so far and the problems you have. I suspect this is a homework problem and don't want to just give you a solution. And, don't bother wasting your time telling me it is not a homework problem. I won't believe you. EDIT: By the way, that DB schema is not very good. Having columns for CatA, CatB, CatC and CatD could be problematic. Would there always be exactly four categories for each record and no need to increase or decrease the number in the future? If no, then the schema is wrong. Also, is there a legitimate distinction between CatA, CatB, etc. or is it just a way to allow four categories? If there is no real significance between the four, then the schema is wrong. Not to mention that you should be using a date field for the month instead of a string. What happens when the year changes? Plus, you can't ORDER the results correctly based on name. The schema probably needs to be more like this: id | date | cat 1 2014-1-1 23 2 2014-1-1 34 3 2014-1-1 33 4 2014-1-1 78 5 2014-2-1 21 6 2014-2-1 38 7 2014-2-1 67 8 2014-2-1 78 etc . . .
  24. Psycho
    Maybe I misunderstood some of your requirements. As far as giving options for varying price, efficiency etc. you would have to add additional details about the "parts" and how they are used in the calculation. Assuming the parts already have a price associated with them. So, you would want some way to use an existing value (e.g. diameter) or assign a score to the parts for any other attributes you want to compare - such as efficiency or throughput. This really take a detailed analysis of the possible parts and how they affect the formula in order to configure correctly. Plus, I assume there are some dependencies between parts. For example, you can't just switch out a 2in valuve for a 4in valve on the same pump without necessitating other changes. So, you may have to have an option to increase the diameter for all parts. Here's an example of how the problem could be approached. Let's take the formula for flow rate: Flow Rate = 1/4 X π X (pipe diameter)^2 X velocity You could show options for greater efficiency based on parts with a higher diameter. "How" that option is displayed to the user is inconsequential. It should "just work". I suspect it would take a lot of time, analysis and coding to create the right framework. But, if done correctly, it shouldn't take any code changes to maintain.
  25. Psycho
    Whether the current solution is too convoluted or not is hard to say. If there are requirements to have some things "dynamically" change on the web page without a submission and/or refresh then you will need to have some integration between PHP and JavaScript. And the preferred method of exchanging data between the two is JSON. But, it could still be more complicated than it needs to be. It's really hard to provide much guidance without some knowledge of how the variable configuration options work. But. I have some experience with variable products that may help. I would start with a table of "options". This would just contain basic details such as the name of the option, default value, and the "type". The type would correlate to field types such as text, radio button group, check box, select list, etc. Depending on the type, they may require additional details. So, for the "select" types there would be a supporting table where there would be entries for the possible values for the select list. Likewise, a radio group would have something similar. A text type may have a supporting table with data such as max length (or whatever variable information about the options that you feel is necessary). You can then use the options table and the associated option details table to build your forms. If you are building a form from scratch, use the default values defined for each option. If you are recreating a form, pull the saved data and build a form using those values to populate the form. I would create a function for each field type for the purpose of creating the output. So, a function for a select list would take parameters for things such as the name of the field, an array of the possible value and the selected value. Either pass the default value or the saved value as the selected value. The function would create the select list and set the appropriate value. Now, if you want the user to be able to interact with the form and have content update dynamically without a page submission, then you do need to use ajax. It's typically considered a better approach to pass data back and forth and have JavaScript act upon that data. But, it may be easier to have PHP generate the HTML and pass it to the JavaScript to update the page. But, even if you were to just pass JSON to the JavaScript code to create the dynamic content, once it is done - it is done. If done properly, you can add/edit/delete options to your hearts content in the database and it should require no reaction in the code.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.