Using a database such as MySQL, Oracle or SQLite to store your website's data? Learn about common issues and pitfalls, the correct use of indexes, normalisation and much more.
Welcome to the second in our series of 'Talk With The Experts' live forum workshops. Next up is WordPress with SitePoint developers Harley Alexander (front end and WordPress) and Bernard Peh (PHP and WordPress) along with assorted Advisors from here at the forums. The session will kick off at...
Jodie Moule (author of our upcoming UX book) is looking for some SitePoint community members to interview for her book. She is specifically interested in what it is that you want from a UX book. If anyone is interested in taking part, please PM me your name and email address and I'll hook it up.
PostgreSQL -what are the best resources on the net? Can anybody submit good links, tutorials, links to references, and recommended books thanks, DH
I thought I'd better make sure that everyone is aware of a particular class of security vulnerabilities called malicious SQL code injections. While there are many other security vulnerabilities, these are particularly dangerous - left unchecked, a malicious user could access or otherwise manipulate...
in this thread will be posted various Best Practices and Other Useful Infomation, gleaned from the experienced members lurking around the forums and good other external resources. please observe the following when posting in this thread: make sure to create a good title when replying to this...
If you are reading this you are the SitePoint member we're looking for. The SitePoint forums are currently working on improving the sticky threads. Much work has already been done towards this goal within the "Program Your Site" team. And we are acutely aware of some of their problems. But so...
Useful Tips The members of the Programming Team offer the following suggestions to help you get the most from your time here. 1) Try a search. Your problem or a similar one has probably been discussed/answered before. SitePoint has two search features. The vBulletin search...
Useful Tips The members of the Programming Team offer the following suggestions to help you get the most from your time here. 1) Try a search.Your problem or a similar one has probably been discussed/answered before. SitePoint has two search features. The vBulletin search...
Here are some excellent articles on database design & normalisation - most are written with Access in mind, but 99% of the principles and techniques apply to every database under the sun, so they are well worth a read no matter which DB you use! Understanding Relational Database Design ...
This is a document I've been slowly adding to as I come across things 'in the wild'. Maybe it can be of some use? SQL and RDBMS Database Design DO's and DON'Ts ------------------------------------------ Generic DOs: Develop a backup strategy before you encounter a catastrophic database...
Hi, I have table: plan which has fields : PCODE, max_lot, min_lot sample data: PCODE = P35 max_lot = 5 min_lot = 3 PCODE = P35M max_lot = 5
I am working on a system where I have a common table (Node) which has a few basic attributes (basically handles the relationships). There are various tables which "extend" this table (in an OO sense), which then go into specific things (such as Forum or Thread), which has specific data. My...
Good day! Hi, I encountered problem in Count. I have this data: REFNUM----LOT_CODE----PCODE--wip_chemicalweighing--wip_compounding-------wip_extrusion--wip_forming--wip_deflashing
I have two very simple tables, User and Ecosystem: User - ID - loginName # ... Ecosystem - ID # ...
hi folks - I was hoping someone (Rudy?) could help me understand what I'm missing here; as I mentioned to Rudy earlier I know enough about SQL and databases in general to be dangerous. I have two tables: customers & visits; one customer can have multiple visits; what I'm trying to come up with...
Hi Guys! I'm trying to add <li></li> tags around rows that I get from the database. Here's the query i'm using but it doesn't work. The left join on the "users_certifications" is the part causing the problem. Is my sql wrong? SELECT users_profile . * , users.first_name, users.last_name,...
Hi all I am not very advanced in database scripting/programming, but have a small issue: This code is created for a wordpress plugin widget, <prefix> is the table prefix as set in wordpress. For what I need to do, I need to query 4 tables (<prefix>wp_eMember_members_tbl,...
I would need to be able to use this function in the WHERE clause instead than in the SELECT clause. Is there a way to do this? Meaning, to find the maximum value of a column used in the WHERE clause?
i have the below select * , ,ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID from (select top 10 * ,ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID from (select top (30) * ,ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID from EMPLOYEE order by NAME ) as T1 order by T1.NAME ...
What is normalization in Database
I'm looking for suggestions on how to design a table to save user's IP addresses. I will be saving the last 20-50 IP addresses used. I will not be storing duplicates (unless that is the most efficient way to go here), but I want to record how many times each IP address was used. (So if I check to...
I have a database (MySQL) and I need data from n databases (probably multiples DBMS). For example, if a person is added as an user in other database i need to update my table with the new name. The data is critical, and for sure, i only would have access to read. My database is the only that...
hi all. how to transform this algorithm in MySQL ? : Start1 (from 2011/01/01 to 2011/02/20) = 10284 + (9749*20/28)=17247,6 Start2 (from 2011/01/01 to 2011/02/20) = 7045 + (7475*20/28)=12384,3 would be a progressive calculation today reported on previous year.
Hi This is my table: -- -- Table structure for table `faq_categories` -- CREATE TABLE IF NOT EXISTS `faq_categories` (
Hi everyone, I need your help. I try this query in MySQL: SELECT COALESCE(`Zn`,'Tot') `Zn` , COALESCE(`description zn`,'Tot') `description zn` , `tot inf` , `tot hh o` , `tot hh i`
I am trying to streamline my PHP code by making only one search field instead of having several. This one field will allow you to type in a person's ID, part of their name, email, etc. Here is my query: select date_format(registerDate, '%m-%d-%Y') as registerDate, uID, firstname, lastname,...
Hello all. Good evening. I currently have a website were you post comments, but I want there to be a way for people to make replies to those comments. I'm new to SQL and PHP, but I think the way to do this would be to make a new table for the replies and have an ID that connects to the original...
Hello Everyone.. I am looking for designing a database structure for a treeview plugin. The nodes should be populated from the database. The root node can have unlimited level of child nodes and each child node can further have unlimited level of sub-child nodes. Can someone please suggest...
I have a master table and a detail table and want to perform a query returning only a certain number of master records that can be processed as a limit so I can paginate the full set. So if each master has a different number of detail records, putting a limit on the query limits it based on the...
Hi everybody, I am struggling to set up an EVENT in mysql - But it doesnt execute the commands, and I have spend hours to figure out why by now. I hope to find the answer here :-) This is the code for the event: delimiter | CREATE EVENT delete_cart
Hi all, I am working on a project where I have 900+ tables in a existing db and I need to know the schema for the DB. If I use desc (SHOW tables); it gives me an error If I use SELECT <TABLE_NAME1> from dba_tables; it results in schema for only TABLE_NAME1. I need this o be done for...
Hello guys! This is my first post and I am excited to be on the most famous forum site on internet... :D Our Database Management Systems course instructor asked us what would we be creating for our semester project, and I replied here that I am interested in creating a forum that runs in our...
I need help here and I have been racking my brain over this. I am actually using Dreamweaver Cs3 to do the search over two tables in MYSQL . The search works if I am using "OR" in the query and it gives me the exact results but it does not show any result if I used "AND" in the SQL statement. I...
I would like to store a User's IP Address and Hostname. What Data-Type and Field-Size should I be using? I am using the following PHP functions... // Capture User's IP Address. $userIPAddress = $_SERVER; // Capture User's Host Name.
What is the best way to connect an entry in a database table with multiple entries in the same table? I was thinking about creating a table to store this information, but is there a better way to do it?
Hi all, I have a MySQL database which needs to be optimized which option works best - Server Tuning or Normalization? Is there any reason why you would use only one option - would it not be better to work at both? Thanks, Andy.
I put a lot of work into optimizing our database queries over the years, but I always focus on our select queries. Since we are an ecommerce store, the majority of the queries we do are selects from the product catalog and even though the SQL in the selects can be rather large, they are very quick....
When I started my current (and first) developer job I inherited a database table naming convention which used prefix of 't' on every table eg tOrders, tProducts etc. Is there any point in this? The only use I can see for this is to differentiate tables from views, but is there even any point in...
Let's say I have the following query in mysql: SELECT net_price, net_price*tax AS price, net_price*tax*qty AS total, net_price*tax*qty*discount AS discount_total FROM product In this sample only the first returned column is actual data from the table, the rest are values calculated on...
hi.. Im new to mysql..i want to know how to generate daily reports for all employees i created 3 tables with fields employee table:emp_id,name job table:job_id,job_name activity table:act_id,emp_name,job_name,date,activity done(assigned values as email,phonecall,visits)
Im using this php code to see if I can connect to my database. $username="fixmy1_luke"; $password="xxx"; $server="localhost"; $database="fixmy1_shores-rentals"; $db_handle = mysql_connect($server, $username, $password); $db_found = mysql_select_db($database, $db_handle);
Does the order of Fields affect the performance of a Table? For example, if my table has... ...and I moved "email" - which is a commonly used and important field - to the end like this... ...then how would that affect the performance of my Table and Queries?
Hi there, I have tried lots of different ways to get what i want out of this query but i seem to be failng all the time and if anyone could PLEASE help me!! I am trying to basically do a query that says if there are no events in the current month then please return a value of "no records" or...
i have this error DB Error: Duplicate entry 'root' for key 2 : DB Error: Duplicate entry 'root' for key 2
I have several fields, one of which is a BLOB. I want to SELECT the fields and return the results to the browser, and then dump the contents of the BLOB into a file on the server. I'm struggling with how to do this in one query. The on_linear_array field is the BLOB. SELECT id ,model_type...
Hello all, I have read some tutorials on MYSQL stored procedures and find it very difficult to get my head around it. My first query is: SELECT aChild.id, aChild.type, GROUP_CONCAT(aClient.client_id) AS `client_ids`, count(aClient.client_id) AS `count` FROM Asset_Child AS aChild JOIN...
Hi there, I am looking for a tool to compare schema s of two different dbs. I want to compare them on local host - apache on my local disk , I am running mysql 5.1. I have tried downloading tools like dbdif but I have no idea how to install them because I think they need to be installed with...
Ok lets say I have a page and the body of that page is made up of blocks of text, lets call them sections. Each section is made up of a title and a body text, it may also have sub-sections containing also a title and body text. So we have something like this: <page> <section> <section>...
I want to write a query to generate attendance report of employee. First I will tell you how the presence of employee is stored in my database. I have following tables. Employee Table with Columns emp_id emp_Name Joining_Date 1 john 11-01-2012 2 Scott ...
Use this control to limit the display of threads to those newer than the specified time frame.
Allows you to choose the data by which the thread list will be sorted.
Order threads in...
Note: when sorting by date, 'descending order' will show the newest results first.
Forum Rules
