Search the Community
Showing results for tags 'mysql'.
-
In a current project I have a large database that basically holds people and their personal information e.g. each contact can have multiple phone numbers, email addresses, residential addresses etc... As a result data security and integrity are very important. I started of sketching out the database schema and it seems fairly straight-forward. I'd create a people table which contains the individual people and then a series of other tables to hold their personal information each in a one-to-many relationship. E.g. Each person can have multiple phone numbers. All seemed fine until I started thinking through the use cases. If someone updates their mobile number and saves their details I would essentially have to delete all their numbers and then add them all again because you can't easily tell if details were deleted, updated or inserted to the contact. I think any effort to package the existing ID with each number would simply be messy. So first, is there anything wrong with deleting and re-inserting all one-to-many relationships? It will lead to big gaps in the primary key which I guess can be fixed by calling something like repair etc... For reference this database will have several million users. This seemed messy so I considered storing the one-to-many relationships as serialised objects within a single table because then I wouldn't have to worry about maintaining FK relationships and having large gaps in the primary keys. This led me to looking at NoSQL solutions as this seems to be basically what they do. I read the mogoDB article from Larry's newsletter and thought it might be worth considering. Any thoughts? PS. In general I only have to update or fetch the objects I don't have to search based on a phone number etc...
-
I'm just trying to use an alias in a select query and I'm having a problem. It's not a big deal, but I don't know why I'm getting an error. Can anyone answer this: Why does SELECT title, text, id FROM memos LEFT JOIN memos_associations USING (id) WHERE CONCAT_WS(' ',title,text) REGEXP '[[:<:]]potassium[[:>:]]' = 1 work ok, but SELECT title, text, id, CONCAT_WS(' ',title,text) AS combo FROM memos LEFT JOIN memos_associations USING (id) WHERE combo REGEXP '[[:<:]]potassium[[:>:]]' = 1 not? The latter generates the error, "ERROR 1054 (42S22): Unknown column 'combo' in 'where clause'." Thanks! ...Jason -
On line 33 it reads: $words = FALSE; // Flag variable - Why is this needed? Why false boolean? On line 37 it reads: $r = msyqli_query($dbc, $q); if (mysqli_num_rows($r) == 1) { $words = mysqli_fetch_array($r, MYSQLI_ASSOC); } - Is this common practice, to check to see if there is at least one result returned from the query? In some scripts, this is included and then in some it is. For example go to line 47 of the same script, when the array is fetched the function isn't wrapped in the same way as above. Line 46, 47: $r = msyqli_query($dbc, $q); $r = mysqli_fetch_array($r, MYSQLI_ASSOC); -
This is my php code like the admin code in ecommerce chapter of book PHP 6 and mysql. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Add a dress</title> </head> <body> <?php ini_set ('display_errors', 1); require_once('../../mysqli_connect.php'); if (isset($_POST['submitted'])) { $errors=array(); if(!empty($_POST['dress_name'])) { $dn =trim($_POST['dress_name']); } else { $errors[]='please enter the dress name!'; } // Check for an image: if (is_uploaded_file ($_FILES['image']['tmp_name'])) { // Create a temporary file name: $temp = '../../uploads/' . md5($_FILES['image']['name']); // Move the file over: if (move_uploaded_file($_FILES['image']['tmp_name'], $temp)) { echo '<p>The file has been uploaded!</p>'; // Set the $i variable to the image name: $i = $_FILES['image']['name']; } else { // Couldn't move the file over. $errors[] = 'The file could not be moved.'; $temp = $_FILES['image']['tmp_name']; } } else { // No uploaded file. $errors[] = 'No file was uploaded.'; $temp = NULL; } // Check for a color : $c=(!empty($_POST['color'])) ? trim($_POST['color']):NULL ; // Check for origional price: if (is_numeric($_POST['origional_price'])) { $op = (float) $_POST['origional_price']; } else { $errors[] = 'Please enter the dress price!'; } // Check for our price: if (is_numeric($_POST['our_price'])) { $rp = (float) $_POST['our_price']; } else { $errors[] = 'Please enter the dress price!'; } // Check for a description (not required): $d = (!empty($_POST['description'])) ? trim($_POST['description']): NULL; // Validate the designer... if (isset($_POST['designer']) && ($_POST['designer'] == 'new') ) { // If it's a new designer, add the designer to the database... // Validate the first and middle names : if (!empty($_POST['first_name'])) { $fn = trim($_POST['first_name']); $mn = (!empty($_POST['middle_name'])) ? trim($_POST['middle_name']) : NULL; // Check for a last_name... if (!empty($_POST['last_name'])) $ln = trim($_POST['last_name']); // Add the designer to the database: $q = 'INSERT INTO designers (first_name, middle_name, last_name) VALUES (?, ?, ?)'; $stmt = mysqli_prepare($dbc, $q); mysqli_stmt_bind_param($stmt, 'sss', $fn, $mn, $ln); mysqli_stmt_execute($stmt); // Check the results.... if (mysqli_stmt_affected_rows($stmt) == 1) { echo '<p>The designer has been added.</p>'; $designer = mysqli_stmt_insert_id($stmt); // Get the designer ID. } else { $errors[] = 'The new designer could not be added to the database!'; } // Close this prepared statement: mysqli_stmt_close($stmt); } else { // No last name value. $errors[] = 'Please enter the designer name!'; } } elseif ( isset($_POST['designer']) && ($_POST['designer'] == 'existing') && ($_POST['existing'] > 0) ) { // Existing designer. $designer = (int) $_POST['existing']; } else { // No designer selected. $errors[] = 'Please enter or select the dress designer'; } if (empty($errors)) { // If everything's OK. // Add the dress to the database: $q = "INSERT INTO dresses (designer, dress_name, origional_price, our_price, color, description, image_name) VALUES (?, ?, ?, ?, ?, ?,?)"; $stmt = mysqli_prepare($dbc, $q); mysqli_stmt_bind_param( $stmt, 'isdsss', $designer, $dn, $op, $rp, $c, $d,$i); mysqli_stmt_execute($stmt); // Check the results... if (mysqli_stmt_affected_rows($stmt) == 1) { // Print a message: echo '<p>The dress has been added.</p>'; // Rename the image: $id = mysqli_stmt_insert_id($stmt); // Get the dress ID. rename ($temp, "../../uploads/$id"); // Clear $_POST: $_POST = array(); } else { // Error! echo '<p style="font-weight: bold; color: #C00">Your submission could not be processed due to a system error.</p>'; } mysqli_stmt_close($stmt); } // End of $errors IF. // Delete the uploaded file if it still exists: if ( isset($temp) && file_exists ($temp) && is_file($temp) ) { unlink ($temp); } } // End of the submission IF. // Check for any errors and print them: if ( !empty($errors) && is_array($errors) ) { echo '<h1>Error!</h1> <p style="font-weight: bold; color: #C00">The following error(s) occurred:<br />'; foreach ($errors as $msg) { echo " - $msg<br />\n"; } echo 'Please reselect the dress image and try again.</p>'; } ?> <h1>Add a dress</h1> <form enctype="multipart/form-data" action="add_dress.php" method="post"> <input type="hidden" name="MAX_FILE_SIZE" value="524288" /> <fieldset><legend>Fill out the form to add a dress to the catalog: </legend> <p><b>Dress Name:</b> <input type="text" name="dress_name" size="30" maxlength="60" value="<?php if (isset($_POST['dress_name'])) echo htmlspecialchars($_POST['dress_name']); ?>" /></p> <p><b>Image:</b> <input type="file" name="image" /></p> <div><b>Designer:</b> <p><input type="radio" name="designer" value="existing" <?php if (isset($_POST['designer']) && ($_POST['designer'] == 'existing') ) echo ' checked="checked"'; ?> /> Existing => <select name="existing"> <option>Select One </option> <?php // Retrieve all the designers and add to the pull-down menu. $q = "SELECT designer_id, CONCAT_WS(' ', first_name, middle_name, last_name) FROM designers ORDER BY first_name, last_name ASC"; $r = mysqli_query ($dbc, $q); if (mysqli_num_rows($r) > 0) { while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) { echo "<option value=\"$row[0]\""; // Check for stickyness: if (isset($_POST['existing']) && ($_POST['existing'] == $row[0]) ) echo ' selected="selected"'; echo ">$row[1] </option> \n"; } } else { echo '<option> Please add a new designer. </option>'; } mysqli_close($dbc); // Close the database connection. ?> </select> </p> <p><input type="radio" name="designer" value="new" <?php if (isset($_POST['designer']) && ($_POST['designer'] == 'new') ) echo ' checked="checked"'; ?> /> New => First Name: <input type="text" name="first_name" size="10" maxlength="20" value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" /> Middle Name: <input type="text" name="middle_name" size="10" maxlength="20" value="<?php if (isset($_POST['middle_name'])) echo $_POST['middle_name']; ?>" /> Last Name: <input type="text" name="last_name" size="10" maxlength="40" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name'];?>" /></p> </div> <p><b>Origional Price:</b> <input type="text" name="origional_price" size="10" maxlength="10" value="<?php if (isset($_POST['origional_price'])) echo $_POST['origional_price']; ?>" /> <small>Do not include the dollar sign , commas or Rs.</small></p> <p><b>Our Price:</b> <input type="text" name="our_price" size="10" maxlength="10" value="<?php if (isset($_POST['our_price'])) echo $_POST['our_price']; ?>" /> <small>Do not include the dollar sign, commas or Rs.</small></p> <p><b>color:</b> <input type="text" name="color" size="30" maxlength="60" value="<?php if (isset($_POST['color'])) echo htmlspecialchars($_POST['color']); ?>" /> (optional)</p> <p><b> Description: </b> <textarea name="description" cols="40" rows="5"> <?php if (isset($_POST['description'])) echo $_POST['description'] ?> </textarea> (optional) </p> </fieldset> <div align="center"><input type="submit" name="submit" value="Submit" /> </div> <input type="hidden" name="submitted" value="TRUE" /> </form> </body> </html> these are the errors: Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in C:\xampp\htdocs\admin\add_dress.php on line 140 Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in C:\xampp\htdocs\admin\add_dress.php on line 141 Warning: mysqli_stmt_affected_rows() expects parameter 1 to be mysqli_stmt, boolean given in C:\xampp\htdocs\admin\add_dress.php on line 145 Your submission could not be processed due to a system error. Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in C:\xampp\htdocs\admin\add_dress.php on line 166 please help me i ve been trying to solve it for three days but no use -
Mentioned in the book in especially Chapter 14: Making Universal Sites and Chapter 15: Message board example, are recommendations for processing unicode safe data. We learn that utf-8 supports a wide list of languages. There was so much information to digest, I could not recall if every action is required when building a universal/multilingual site. Are all of these set by default? Particularly the database character set. My mySQL is already set as (actually copied this straight from phpMyAdmin) utf8_unicode_ci Unicode (multilingual), case-insensitive Would I then have to establish a charset and collation for the database? Are these actions needed each time, here is a list I gathered up while reading. Added to inbetween the head tags <meta http-equiv="content-type" content="text/html; charset=utf-8"> -> Page 416-417 Must be the first line before any HTML, must be a php script page header('Content-Type: text/html; charset=UTF-8'); -> Page 452 (and more information in Chapter 14) Mysql Client CREATE DATABASE forum2 CHARACTER SET utf8; -> Page 444 mysql_connection mysqli_set_charset($dbc, 'utf8'); or mysqli_query($dbc, 'SET NAMES utf8'); -> Page 450 Any one with some insight on this great appreciated with any comments. -Mark
-
A database design example commonly available pressumes that one wants to organize a collection of books, and depicts the usual author, title, isbn, etc., breakdown, and may even normalize to several tables. Generally not seen, however, is an example that includes the books contents, i.e., chapters, headings, footnotes, and body copy Any thoughs on why this is? Would MYSQL even be capable of managing the task? ~ David -
I’m creating the auction site example from the last chapter of Modern Javascript. It’s a very good example to test one’s understanding of php and mysql queries. I’m struggling with the conversion of timezones. All my queries work fine except when I try to use CONVERT_TZ on any of the dates. For example the following query returns all my rows with a value for itemId, item and bid but null for the column which converts the date to the user's timezone. SELECT itemId, item, FORMAT(COALESCE(MAX(bid), openingPrice),2), IF (CONVERT_TZ(dateClosed, 'UTC', 'Asia/Tokyo') < DATE_ADD(UTC_TIMESTAMP(), INTERVAL 24 HOUR), DATE_FORMAT(dateClosed,'%l:%i %p'), DATE_FORMAT(CONVERT_TZ(dateClosed, 'UTC', 'Asia/Tokyo'), '%M %D @ %l:%i %p')) FROM items LEFT JOIN bids USING (itemId) GROUP BY itemId ORDER BY dateClosed I'm starting to think that I need to configure php or mysql in some way to deal with timezone conversion but I'm not sure in which folder to look for a timezone file. I did try entering this command in the terminal, and restarted MAMP but that did not work either. # mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Any ideas? -
I have been seeing more and more 'mysqli' stuff around instead of 'mysql' commands. I finally searched it and came up with that it is 'improved'. I did not feel the need to know much more after reading that 'mysql' would be deprecated by the release of PHP 6, so I looked into converting to 'mysqli'. In a simple search, I found this converter tool on a wiki page by mysql.com: http://forge.mysql.com/wiki/Converting_to_MySQLi. I backed up one of my directories and tested it locally. I was given this list of results of which files it altered, how many mysql_* functions were found, etc. (what I find ironic is that I put the converter in the directory I converted, and I got only warnings for files in that directory). Anyways, when I went to see how my scripts ran on Firefox, they turned out identical. However, as I check the source, I have yet to find an actual adjustment. Did this 'converter tool' work? How should I convert to mysqli? Thank you in advance, Lingolatz -
I am using what I have learned from the Intro to Databases chapter of the book to create a table that will hold user credentials: id, firstname, lastname, username, email, hash, timestamp are the columns. For the registration page, I want the user to submit the data firstname, lastname, username, email, and the password which will become hash. (hash being the md5($_POST['password']) string). More to the point, I want a mechanism to check whether or not the user's desired username is already taken or not. I initally went to page 361 (Retrieving Data from a Database). I found this code, thinking I could use it as a solution: SELECT * FROM users WHERE name='Larry' I figure that I can alter it to read: SELECT * FROM credentials WHERE username="{$_POST['username']}" (credentials being the table containing the relevant data). That code would of course be stored in a $query variable. Would I have to place the code in a loop? I do not think I would have to, since there could only be one username in the table that could match the desired username. How would I go about checking whether the query (or loop) returned results or not? Are there any things I can do to make my current system better? Thank you in advance, Lingolatz
-
Hi! This is not a question as such, I am mainly looking for somebody to tell me whether I am on the right track or way off the mark. I am currently working through the MySQL chapters, in particular Normalization. i just want to check if I have understood everything corrctly so far. Say I am creating a "language skills database" for an office, where all of the staff speak one or more foreign languages. Staff_id (PRIMARY KEY) first_name last_name email telephone number start_date language The way I understand normalization, I would need to first create table for the staff, removing the language column as each member of staff my speak more than one foreign language (French, German and Spanish for instance) which would be in violation of 1NF (i.e. atomicity) ie.: Staff_id (PRIMARY KEY) first_name last_name start_date This table would be in 1NF, as each entry has a Primary key, each field is atomic and each record will appear only once. I would then have to create a "Language Skills" , i.e. lang_id (PRIMARY KEY) language Again, I understand that this table would be 1NF. The Relationship between the two tables is currently M:M i.e. one memeber of stafff can speak mutiple languages, and each language (French for instance) may be spoken by many members of staff. I would therefore require a thrid table (a link / intermediary table) along the following lines: Staff_id (FOREIGN KEY) lang_id (FOREIGN KEY) whereby the link from the staff table to the link table is 1:M and the link from the Languages table to the link table is also 1:M So then by now my database is 1NF (no listed fields, and each table has a primary key) and 2NF (any columns that would contain duplicated data - ie. the languages spoken by the staff - have been slipt off into a new table - i.e. the languages table) Does this sound about right? All comments and tips will be very much appreciated. Thanks LF -
Hi, I hope someone can help here, as I'm starting to pull my hair out. I've been trying to install the timezones that are discussed in Chapter 6 to my MySQL installation that is part of MAMP. If I run the following command while in the /Applications/MAMP/Library/bin folder: mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -p -u root mysql Terminal gives me the following error messages: -bash: mysql_tzinfo_to_sql: command not found -bash: mysql: command not found Forgive me if I'm being daft and this something simple, but I just can't figure out what the problem is. The relevant version numbers are below: MySQL 5.5.9 Mac OS X 10.7.3 Thanks in advance for any help that anyone can offer. -
I have a client who needs to move their website to another host. They have a forum using vBulletin software. It's been active for many years and is very large. I'm trying to export the mysql from the database so that I can rebuild the database on the new servers, but whenever I try to export it I just get an empty, blank document. I suspect, but I'm not really sure, that maybe it's so big the current settings won't allow enough time to complete the export. Does this sound possible? If so, how would I fix this? If not, what other things could be causing this. The phpMyAdmin interface says version 2.8.2.4 and the MySQL says version 5.0.45. Bottom line, I need to get the database exported, so any suggestions would be very welcome. Thanks much for any help. -
Hello, I have three tables in my database: ingredients , categories, and an intermediary table: ingredients_categories. Let's say I inserted id_ingredient 100 to 150 into ingredients. Right now, if I want to populate the intermediary table I use two queries: INSERT INTO ingredients_categories (id_ingredient) SELECT id_ingredient FROM ingredients WHERE id_ingredient BETWEEN 100 AND 150; UPDATE ingredients_categories SET id_categorie=11 WHERE id_ingredient BETWEEN 100 AND 150; Is there a smarter way of doing this, with just one query? With thanks for your help, -
Hi all, what does the key ``(``) for? Thanks
-
Hello, Could someone help me with a regular expression in MySQL? If I test the following pattern in PHP: \b(\')?((eau){1}|(eaux){1})\b the results are fine since I get a match for but no match for Yet if I try the very same pattern in the database, using phpMyAdmin, I get no results at all although all the above examples are in the database. Could someone explain why this is happening, and what I need to do in MySQL? In case you wonder why I'm not using a FULLTEXT search, I plan to use regular expressions for words that are 1, 2 or 3 characters long, and a FULLTEXT search for longer words or expressions. With thanks for your help,
-
I have a MySQL database model where I have different types of users, each having common field names, like 'username', 'password', 'first_name', 'last_name', etc. etc. I've designed it so that these common fields are stored in a 'base' table called 'users' and specific fields that apply to each type of user are stored in derived 'sub-tables' for each type of user. Here is what it looks like: I know that this is a better design than having the 'common' fields stored in every 'user' table. The problem I'm having is I'm trying to figure out how I'm going insert a new user. What would be the best way to handle this? Initially, I'm thinking I could do an insert like this (let's say I wanted to insert a student): <?php // Begin Transaction // Insert the common data into the 'users' table // Get the last inserted ID // Insert the student-specific data into the 'students' table along with the last inserted ID // Commit // If problem occured // Rollback ?> But that just seems like a very crappy way to do it. I would like to have it done in one swift statement. So I haven't had much luck finding clear solutions online, but I recall one person mentioning the use of updatable views for each subtype, where the view would perform an inner join on the subtype table and the base table, and you could insert and update using the single view. I have tried to create a view but keep getting the error: #1356 - View '[view name]' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. Where [view name] is the name of my view. I get this error even when just selecting one column from one table and I know the names are right. Would the 'view' approach be the best way to go, assuming I can figure out how to get it to work? Is there a better way than what I've mentioned so far? Much thanks, Zane EDIT: Oh, and the PK 'user_id' is an auto-incremented INT in the users table. EDIT 2: Got my view to work. Turns out I had to specify the SQL SECURITY line as INVOKER instead of the default DEFINER. Going to try to see if I can perform inserts and updates on this view... -
MySQL Version: 5.0.45 PHP Verson: 5.2.17 I am using a code modified from chapter 17. but I cannot get mysql to display query results using php. I made a page to make a summary (title and date only) page that turns the title into a link to the detailed page it is working correctly when i click on entry #4 (all work the same way) for example my url seems to be correct ...blogpage.php?pid=4 but I get this result at the top of the resulting page SELECT entry_id , title , date_updated , p1 , p2, FROM blog WHERE entry_id = 1 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in **redacted**walterlovett/htdocs/cityofwinfield/blogpage.php on line 15 (shows as row 10 in the inserted code (while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {) this is my mysql table structure entry_id int(5) No (auto increments) date_updated timestamp No CURRENT_TIMESTAMP (auto updates) title varchar(40) No Entry Title (default text) p1 text No p2 text No PRIMARY entry_id here is my code <?php // This page displays the details of the post. $row = FALSE; // Assume nothing! if (isset($_GET['pid']) && is_numeric($_GET['pid']) ) { // Make sure there's an entry ID! $pid = (int) $_GET['pid']; // Get the post info: require_once ('../../mysqli_connect_city_of_winfield.php'); echo $q = "SELECT entry_id , title , date_updated , p1 , p2, FROM blog WHERE entry_id = $pid"; $r = mysqli_query ($dbc, $q); while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) { // Fetch the information: $row = mysqli_fetch_array ($r, MYSQLI_ASSOC); // Start the HTML page: $title='Unit Details'; include('includetop.html'); echo "<p>{$row['title']}</p>"; echo "<p>{$row['date_updated']}</p>"; echo "<p>{$row['p1']}</p>"; echo "<p>{$row['p2']}</p>"; } // End of the mysqli_num_rows() IF. mysqli_close($dbc); } if (!$row) { // Show an error message. include('includetop.html'); echo '<h1>Error 404; File Not Found<br></br> The page you are looking for has most likely been upgraded, <br></br> please use the navigation at the left to find what you need,<br></br> Thanks.</h1>'; } // Complete the page: include('includefooter.html'); ?> when the page is called i get the "else" option (my little 404 message in the code above) and this is printed at the top of the page (i just put the echo of the query in there to help me debug) SELECT entry_id , title , date_updated , p1 , p2, FROM blog WHERE entry_id = 1 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in **redacted**walterlovett/htdocs/cityofwinfield/blogpage.php on line 15(shows as row 10 in the inserted code (while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {) when i run that query in mysql phpadmin i get this response from mysql Error SQL query: SELECT entry_id, title, date_updated, p1, p2, FROM blog WHERE entry_id =4 LIMIT 0 , 30 MySQL said: #1064 - 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 'FROM blog WHERE entry_id = 4 (the 4 just happens to be the entry i clicked on) LIMIT 0, 30' at line 1 I really can not see what is wrong with my syntax. Any help would be greatly apprecitated walt
