DavidAM

Using the standard process - something like this maybe: $thingID = 21; $sql =sprintf('SELECT SUM(LikesIt) AS LikesVotes, SUM(IF(LikesIt = 0, 1, 0)) AS DislikeVotes, COUNT(UserID) AS TotalVotes FROM WhoLikes WHERE ThingID = %d', $thingID); $res = mysql_query($sql); if ($res) { // We only have one row so we don't need a loop $row = mysql_fetch_assoc($res); if ($row) { $VotesForLike = $row['LikesVotes']; $VotesForDislike = $row['DislikeVotes']; $VotesTotal = $row['TotalVotes']; } else { // No row returned - Either ThingID does not exist or it has no votes $VotesForLike = 0; $VotesForDislike = 0; $VotesTotal = 0; } } else { // The Query Failed, so do something // you must have typed something wrong because my code is always perfect } It is not strictly necessary to select the COUNT(UserID) because the total votes should always be equal to LikeVotes plus DislikeVotes.

You can pass it as a hidden field on the form: <form action="/doDelete.php" method="POST" onSubmit="return confirmation()"> <input type="button" value="delete record"> <input type="hidden" name="delFile" value="filename.ext"> </form> Then retrieve the value from $_POST['delFile'] (or whatever name is assigned to the hidden field).

Look through you code for other references to $kennels_table it is getting clobbered somewhere.

If you ask, the server will tell you what is wrong. Add mysql_error() to your "error handler". $result = @mysql_query( $query ) or die ("$admtext[cannotexecutequery]: $query\n" . mysql_error()); I do not use nor recommend the error suppression operator (@) and I do not use nor recommend "or die()" -- I just copied your code and added a little bit to it. Note: You do not need the backticks ( ` ) around column names unless they are reserved words. Oh, wait a minute: look at your query, there is no way that query is working at all: INSERT IGNORE INTO (gedcom,kennel) VALUES ("World","Avongara") There is no table name there. It needs to be INSERT IGNORE INTO tablename (gedcom,kennel) VALUES ("World","Avongara") It looks like $kennels_table does not have a value.

$headers = "From: [email protected]\r\n"; mail($email, 'SWG:ANH New account registration/confirmaton', $message, $headers);//LANGINSERT 1) Check your From address above. You have two ".com"s in there. 2) Check the return code from the mail() function 3) Do you have error reporting on? i.e. error_reporting(E_ALL); ini_set("display_errors", 1); -- You might be getting a message that could help explain the problem. 4) Check your Junk mail folder In my opinion, if the mail() function is succeeding (returns true), the most likely problem is the From address you are using. Let's say your website is "www.mydomain.com" and your From address is "[email protected]". Your server might NOT send the message at all because the From domain is not your domain - this is a SPAM indicator and your host does NOT want to get marked as permitting spam. IF your server SENDS the message, your email server may flag it as SPAM because the addresses do not match, and it may be in your spam filtered box - I suppose your email server could just refuse delivery. The short answer is to always specify a From address that belongs to the server that is sending the mail; i.e. [email protected]. Except for the second ".com", it looks like your From address is your server, so that should be OK. You might try removing the "\r\n" from the end of the $headers; I don't think you need it on the last header (and you have only one). You might also try changing it to just a new-line ("\n") -- that's not valid according the the specs, but there are some misbehaved agents out there that automatically inject the carriage-return before the newline. On one of my development boxes (which I had not configured correctly), I had to add the additional parameter of "-f [email protected]" specifically matching the server. So you might try: mail($email, 'SWG:ANH New account registration/confirmaton', $message, $headers, '-f [email protected]');//LANGINSERT

PHP is run on the server, before the page is sent to the user. Javascript is run in the browser. You cannot mix the two. Your PHP can write Javascript to be "added" to the page, but it cannot execute Javascript code. Javascript can issue a request to the server (AJAX) to execute a PHP script on the server and collect the results, but it cannot call PHP code directly. One way to accomplish your goal would be to use an OnSubmit handler for your form. As an example, I will take the code suggested by Rifts and make a few changes <html> <head> <script type="text/javascript"> function confirmation() { var answer = confirm("Delete Record??") if (answer){ return true; } else { return false; } } </script> </head> <body> <form action="/doDelete.php" method="POST" onSubmit="return confirmation()"> <input type="button" value="delete record"> </form> </body> </html> The form has to send a new request to the server to do the deletion. This will be a new script you will have to write. I've named it "doDelete.php", you can call it "fred.php" or "wilma.php" or whatever you want. But that script will have to do the delete. The "onSubmit" attribute of the FORM tag causes the specified function to be executed. This attribute has a "return" command in it, so that whatever the function returns is returned to the form handler (seems redundant to me too, but I didn't write the spec, and the didn't ask me). If the function "confirmation()" returns false, (and we return false to the submit handler) then the default action will not be performed. If we return true, then the default action will be performed. The default action is to send the form data to the script we specified in the action attribute of the form tag. The doDelete.php script will need to send a new page to the user. You can either show a confirmation, or redirect to the original page, or tell them good bye, whatever is appropriate. This functionality could also be accomplished using AJAX without having to refresh the page. But that will be more complicated.

Use the substr() function

Use a multi-column primary key. You do not need an auto_increment on this table. CREATE TABLE WhoLikes ( ThingID INTEGER NOT NULL, UserID INTEGER NOT NULL, LikesIt BOOLEAN NOT NULL DEFAULT 0, PRIMARY KEY (ThingID, UserID) ) Insert a record when the user votes. Set LikesIt to 1 (one) for Likes or 0 (zero) for Dislikes. If the user does not have an entry, they have not voted. With this arrangement, the user can change their mind or vote multiple times INSERT INTO WhoLikes (ThingID, UserID, LikesIt) VALUES (21, 7, 1) ON DUPLICATE KEY SET LikesIt = 1 User ID #7 now Likes ThingID #21. If the user votes again, you are just updating the same value, so nothing changes. If the user votes Dislikes later, INSERT INTO WhoLikes (ThingID, UserID, LikesIt) VALUES (21, 7, 0) ON DUPLICATE KEY SET LikesIt = 0 User ID #7 now DIS-Likes ThingID #21. And he still has only one vote in the table. To get the number of Likes and Dislikes (just in case you are wondering): SELECT SUM(LikesIt) AS LikesVotes, SUM(IF(LikesIt = 0, 1, 0)) AS DislikeVotes, COUNT(UserID) AS TotalVotes FROM WhoLikes WHERE ThingID = 21

You might be able to do it in a single query, but I would be concerned about the transaction log. I'm not sure how mySql handles it, but I have had big update queries like this (on other DB servers) fill the logs and fail and rollback - after running for a few hours -- what a waste of time. Anyway, a statement like this, might work -- UNTESTED INSERT INTO ci_product_specs_temp (id, name, sku) SELECT ID, MAX(IFNULL(IF(Field_1_name = 'name', Field_1_value, IF(Field_2_name = 'name', Field_2_value, IF(Field_3_name = 'name', Field_3_value, NULL), '')) AS name, MAX(IFNULL(IF(Field_1_name = 'sku', Field_1_value, IF(Field_2_name = 'sku', Field_2_value, IF(Field_3_name = 'sku', Field_3_value, NULL), '')) AS sku FROM ci_product_specs GROUP BY ID I think you get the idea, test each field for the field name and use its value if you find it. If you don't find it, set it to NULL, then if the whole IF is NULL, change it to an empty string (that's so that MAX will use any value found instead of NULL). GROUP BY the ID so you should end up with a single row per ID with all of the data. [ You don't technically need the column aliases in there, I just include them to kind of document the code] You'll have to add the other 11 fields in the two statements I provided above, then add the other 12 fields. It's ugly, but it might work.

In 30 years of consulting, programming and database design and administration; the number of times that I have found it more benefical to query data in code instead of at the database could be counted on one hand; even with compiled languages. Database engines are designed to manage the data, they do it very well. If you can write faster data processing, then you should build a new database engine and sell it! IF you design the database correctly AND design your queries correctly, the database engine will provide the optimum processing; this is even more true with a interpreted language such as PHP. When you think you have found a case where you need to do this, you need to review the database design, indexing scheme, and query design; and then FIX THE DATABASE! If you are trying to get one of us to tell you specific cases where you need to do it; we can't. It would depend entirely on the database and the application. If you come across a situation where you think you need to do it, you can post the specific case and, most likely, we will be able to tell you how to improve the query or the indexes or the database design. Text searching is an interesting case. If you are trying to find every article that contains the words; "PHP", "mySQL", "search", and "multiple"; a traditional SQL SELECT, using LIKE ... OR LIKE ... OR ... will scan the entire table. mySql provides a FULLTEXT INDEX capability to assist in this type of query. There are also other solutions available. However, even without the FULLTEXT Index, I am confident that the SQL SELECT using LIKE will perform faster than selecting ALL of the rows from the table, retrieving them one-by-one into PHP, running substr() on the column data (4 times), and keeping the ones that pass. As to splitting tables, one word -- DON'T -- or if you prefer, four words -- DO NOT DO IT! Your example is a very bad idea. Consider this. The user table should have a unique identifier that is used to reference a user in other tables. For optimum storage and performance this identifier is usually an integer (not the user's name). So, if you have a column called LastChangeUser in a table called CustomerAccounts, and it contains the user's ID, say 14287; are you going to do 676 queries to find the user's name? Or are you going to write a UNION query with 676 SELECT's? That will be some UGLY code either way. Also, in a database of the size you propose (which is NOT unusual) referential integrity is going to be critical. You will want (or at least, I would want) to have foreign key constraints between these tables. Splitting the table will make it impossible to define foreign key constraints to the user table. As a result, someone will be able to delete a user and leave rows in other tables that reference the deleted user's ID. If you are in the planning stages of an application that you expect will really boom; and you expect the database to be big; you MUST spend a significant amount of time designing the database. This includes normalization, indexing, foreign keys, other constraints, triggers, storage requirements, etc. [Note: normalization and indexing are a must for any size database] You also need to plan periodic processes: backups, index rebuilds, table analysis, etc. Query plans are based on information that the server stores including number of rows and index cardinality. As tables grow this meta-data can become stale. If the meta-data is stale, query performance can degrade because the plan is based on false assumptions.

Unfortunately, "having all errors on" does not include mySql errors, only PHP errors. Anytime you execute a query, you have to specifically test for an error. Many people use: $res = mysql_query($sql) or die(mysql_error()); However, that will kill the script if there was an error. I usually take the long way around: $res = mysql_query($sql); if ($res === false) { // Some error occured. Handle it or show it trigger_error('mysql Error: ' . mysql_error(), E_USER_WARNING); } else { // Process the results } by triggering an error instead of dieing, on the production server, with error logging on, the error gets logged. During development with error display on, I get to see the error. At any rate, check mysql_error() after your queries in that part and see if there are errors there.

Instead of onFocus (which happens when the user enters the field), I would use onBlur (which happens when the user leaves the field). Put the calculation in an onBlur event for Amount, Discount, and VAT. Then, as they leave the field, the Total is re-calcuated. When the user clicks the Submit button, they effectively leave the field they are in, so the calculation will be done before the form is submitted: <SCRIPT> function reCalcTotal() { document.getElementById('total').value = (document.getElementById('amount').value - (document.getElementById('amount').value * (document.getElementById('discount').value/100))) * (1+(document.getElementById('vat').value/100)) </SCRIPT> ... Amount - <input type="text" name="credit_amount" id="amount" size="30" onBlur="reCalcTotal()"><br> Discount - <input type="text" name="credit_discount" id="discount" size="30" onBlur="reCalcTotal()"><br> VAT - <input type="text" name="credit_vat" id="vat" size="30" value="17.5" onBlur="reCalcTotal()"><br> Total - <input type="text" name="credit_total" id="total" size="30"><br> It is nice to do this so the user can see the total, but you MUST calculate it for yourself on the server. It would be very easy to submit the form with total = 0.01 so I get it for a penny.

Or, if you have 5.0+ use select SQL_CALC_FOUND_ROWS * from data limit 0, 24 followed by another query SELECT FOUND_ROWS() The second query will return the number of rows that would have satisfied the previous query if the LIMIT was not present. $sql = "select SQL_CALC_FOUND_ROWS * from data WHERE categoryID = 4 limit 0, 24"; $resList = mysql_execute($sql); $resCnt = mysql_execute("SELECT FOUND_ROWS()"); $row = mysql_fetch_row($resCnt); $totalAvailable = $row[0]; while ($row = mysql_fetch_assoc($resList)) { // Show the data } echo "There are " . $totalAvailable . " records available for Category 4"; (I just threw the WHERE clause in to show that it can be used)

Hmm, that's strange. Maybe the rewrite engine does not support using the "?" to make the + not-greedy. You could try: ([A-Z0-9][^/]+) which says anything that is NOT a slash.

I'm no RegExpert but ... To determine the backreference number, you count the openning parenthesis from the left. ^category/(([A-Z]|[0-9]).+?)/([0-9].+)$ Your first backreference is capturing everything between the first set of slashes - "Sports" Your second backreference is capturing the FIRST character after the FIRST slash - "S" Your third backreference is capturing everything after the last slash - "1212112" You don't really need the second capture. The way I read that first group it says: (([A-Z]|[0-9]).+?) First character is a capital letter or a digit followed by any character one or more times wouldn't this work the same? ([A-Z0-9].+?) without capturing the initial character? Also, the second expression: ([0-9].+) inidicates the string needs to start with a digit, but can have any character following it (1 or more times) - were you trying for just digits? If so, I think you need to remove the dot: ([0-9]+) If I'm wrong, someone please correct me. I'm trying to understand these things, but it seems to be a black-art.

When a user TRIES to log in, you should be checking the database to see if the username and password are valid: SELECT id FROM users WHERE username='username' AND password='password' Then you store the ID in the session (like you did with the username and password before). $_SESSION['id'] = $row['id']; Then when you insert the data you pull it from the session: INSERT INTO jobs ( ... , authorid) VALUES(... , $_SESSION['id']) That's all psuedo code, do not cut and paste to your script, it will not run.

I don't see anthing wrong with that part of the code except that these two lines could be causing problems in the browser: echo "<a href=\"$thisroot?pg=$p\"><< prev</a> "; echo "<a href=\"$thisroot?pg=$n\">next >></a>"; When you send "<" or ">" to a page, the browser expects to find a tag. Change those to the html entities "<" and ">": echo "<a href=\"$thisroot?pg=$p\"><< prev</a> "; echo "<a href=\"$thisroot?pg=$n\">next >></a>"; If it still does not work, post your code as it is now.

We really need a better description than "the page breaks". $test = safe_query("SELECT MAX(new_date) FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges UNION SELECT reply_date FROM ".PREFIX."cup_challenges UNION SELECT finalized_date FROM ".PREFIX."cup_challenges) AS maxval"); You are aliasing the psuedo table for your union as "maxval", when I think you actually wanted to alias the MAX(new_date) column: $test = safe_query("SELECT MAX(new_date) AS maxval FROM (SELECT new_date AS new_date FROM ".PREFIX."cup_challenges UNION SELECT reply_date FROM ".PREFIX."cup_challenges UNION SELECT finalized_date FROM ".PREFIX."cup_challenges)"); If that is not the problem, then give us a better description of what is happening. By the way, you do realize you are doing three table scans there (I think). Unless those three date columns are indexed, this query will take a long time to run. I think this is one of the few cases where you might be better off doing a little of this on the front-end. SELECT MAX(new_date), MAX(reply_date), MAX(finalized_date) FROM ".PREFIX."cup_challenges Then find the max of the three returned values - assuming you fetch the data into an array called $row: $maxDate = max($row); That should result in a single table scan. Although, if the three date columns ARE indexed, your UNION solution might be faster. Use EXPLAIN and look through the plan. As to converting the date string. Look at the strtotime() PHP function.

First, do NOT store the user's password in the session. Your application should not need it, and if it does it should get it from the database. Session data is stored in a file and is NOT secure. I usually store the user's ID in the session as well. Then when you insert a record, you can just pull the ID from the session instead of the form.

if (isset($_POST)) { foreach($_POST as $k=>$v) { $_SESSION[$k]=$v; } } If you move this loop up to the beginning of the search() function and add that if(isset()) before it; then change every reference below that from $_POST to $_SESSION; you should be OK. You will need a session_start() at the beginning of the script before you output anything <?php session_start(); ?> <html> <head>

if($_POST['password'] === $_POST['password_again']) { return true; } That code - which I copied from your original post - says: "If the passwords match, RETURN - leave - quit - do NOT run the email check which is the next statement"

The error message indicates the problem is with the INSERT statement that (apparently) puts the user's information into the database. That specific message tells me that the values input by the user were not sanitized properly. The first double-quote mark in the input string caused a problem. So, I don't think the RegExp failed, but I'm not a RegExpert. Find the code that is building your INSERT statement - according to the error message it starts out as "INSERT INTO login (username, password, moderated," - you need to sanitize the user's input before putting it into the query. Use mysql_real_escape_string() for mySql databases, other similar functions exist for other databases. If you need help, post the code and we'll take a look. In the code you posted, we would need to add that function (assuming you are using mySql) in the SELECT statement as well: // CHANGE THIS STATEMENT $existTest = readDatabase("SELECT * FROM login WHERE username=\"$USERNAME\" || email = \"$EMAIL\"",$db); // TO THIS $existTest = readDatabase("SELECT * FROM login WHERE username=\"" . mysql_real_escape_string($USERNAME) . "\" || email = \"" . mysql_real_escape_string($EMAIL) . "\"",$db); // OR THIS (same thing but using single-quotes inside the string so it's prettier) $existTest = readDatabase("SELECT * FROM login WHERE username='" . mysql_real_escape_string($USERNAME) . "' || email = '" . mysql_real_escape_string($EMAIL) . "'",$db); // OR THIS (same thing but using sprintf so it's easier to read) $existTest = readDatabase(sprintf("SELECT * FROM login WHERE username='%s' || email = '%s'", mysql_real_escape_string($USERNAME), mysql_real_escape_string($EMAIL)) ,$db); You need to do this in ALL user supplied data that is put into ANY SQL statement.

In the beginning of your code you are checking to see if the SUBMIT button was POSTed. if (isset($_POST['submit'])) { search(); //call the search function The links for page 2 (and so forth) are NOT using POST, so this IF fails and you show the form. You can get past this by also checking to see if the page number is set in the GET array: if ( (isset($_POST['submit'])) or (isset($_GET['pg'])) ) { search(); //call the search function However Your search code is building the query from the POST array. That array will NOT exist on the second and subsequent pages, so the query will not work as intended. The solution to this problem is not so simple. You will have to store the POST values somewhere (maybe in a session) or add them the the page links so they showup in GET. Either way, you're going to have to rework most of that search() code.

return true; When the passwords match, you are immediately exiting the function, so none of the following statements will be executed.

Your transaction log will tend to get pretty large while running this query. Especially, if you run it against the items table. I'm not sure exactly how mySql handles this, but if you run into problems with the transaction log getting full and aborting the statement (thereby rolling back the transaction after running for 2 hours), you could try putting a limit on the inner query and running it repeatedly until it affects zero rows: DELETE FROM settings WHERE username IN (SELECT settings.username FROM settings LEFT OUTER JOIN users ON settings.username = users.username WHERE users.username IS NULL) LIMIT 1000 play with the limit size to see where a good point is. Also, i hope the username is indexed in the settings table, otherwise, you'll be doing table scans until the cows come home. It sounds like it should be the primary key on that table anyway. Also, if you are using innoDB tables (instead of myisam) take a look at foreign keys and cascading deletes. innoDB is the only table type that supports it. Basically, if you define a Foreign Key from users to settings with CASCADE DELETE; then a delete in the users table will "cascade" (automatically) delete the corresponding entry in the settings table. This is to prevent the problem in the future. You will not be able to define the foreign key until the table is cleaned up.