mysql

Timetable Stats PHP Page

I have a field called dPostTime, which has the date and time stored of an entry. If I wanted to create a page that showed me the number of entries per hour, would I need to have 24 queries, one for each hour, or what's the best way to do this. I'd like the chart to appear as follows: 5-6pm - 24 posts 6-7pm - 56 posts 7-8pm - 34 posts...

How would you construct this SQL query? (MySQL)

Let assume we have these tables: product product_id product_name category category_id category_name product_in_category product_in_category_id product_id category_id how would you get all products that are not in specific category in the product_in_category table (without duplicates). In other words, all products t...

How can I turn a single row column into a scalar in SQL?

This is sort of what I want to do, but MySQL doesn't seem to accept it. SELECT Name, Content, Lft, Rht FROM Articles WHERE (Lft > (SELECT Lft FROM Articles WHERE idArticle = 1)) AND WHERE (Rht < (SELECT Rht FROM Articles WHERE idArticle = 1)); I'm implementing the modified preorder tree transversal algorithm, and I want t...

Whats the difference between using out parameters vs. result fetching by column in stored procedures that return a single result called through JDBC?

I know for more than one result, you don't have much choice for using out parameters, but I'm wondering whats the difference between the following 2 cases where there's one out parameter. Say I have two stored procedures: CREATE PROCEDURE no_out_params(IN foo INT) BEGIN SELECT foo + 1 as result; END and CREATE PROCDURE with_out_pa...

How can I use SUM() to sum my result array?

My current method to add the rows together is like so: $totalxp = $row['Attackxp'] + $row['Defencexp'] + $row['Strengthxp'] + $row['Hitpointsxp'] + $row['Rangedxp'] + $row['Prayerxp'] + $row['Magicxp'] + $row['Cookingxp'] + $row['Woodcuttingxp'] + $row['Fletchingxp'] + $row['Fishingxp'] + $row['Firemakingxp'] + $row['Craftingxp'] + $row...

mysql - adding/concatenating text values within a SELECT clause

Is there a way in MySQL to concatenate text to values using a SELECT statement? (like in Oracle) For example, in Oracle you can write something like this: SQL> select 'The Year is '|| year, 'The month is '|| month from time where rownum < 2; 'THEYEARIS'||YEAR ---------------------------------------------------- 'THEMONTHIS'||MONTH ---...

Using combination of MySQL and MongoDB

Does it make sense to use a combination of MySQL and MongoDB. What im trying to do basically is use MySQl as a "raw data backup" type thing where all the data is being stored there but not being read from there. The Data is also stored at the same time in MongoDB and the reads happen only from mongoDB because I dont have to do joins an...

convert mssql to mysql

How to convert msssql to mysql using mysql workbench. I have already find a manual on the internet but I can't. Maybe I'm using a work keyword. What is the appropriate key word? Is it have another and easy way to convert msssql to mysql. Thank you in advance. ...

editing a row in wpf using linq to mysql

hi friends,upto now i am using mysql database using c# to a edit a row to the table.Bu now i have implemented LinqToMySql for wpf.Bu i can insert a row to the table through linq but i cannot edit that row .my edit code is as follows. Order row; public EditOrder(Order row1) { InitializeComponent(); this.row = row1; ...

Naming conventions - Working with PHP and MySQL

Aloha, I just recently started programming in PHP and have a basic naming convention question. I've read a lot of PHP coding standards after reading this post. It made perfect sense to me, since I already prefer to use camelCase when naming variables. Since I'm going to be working with MySQL, I also read up on Database naming convent...

Displaying error instead of null values in PHP

Hi, I am building up a site in PHP which takes input of more than 20 fields from user. Hardly 3-4 field values are compulsory, rest can be left blank. I want to display a custom error message instead of blank or zero or null while displaying these values. How can this be done? I am using MySQL as backend. ...

geting a variable from public scope to connect database part 2

$hostname['application'] = '127.0.0.1'; $username['application'] = 'root'; $password['application'] = 'root'; $database['application'] = 'band'; $dbdriver['application'] = 'mysql'; class database { private $hostname; private $username; private $password; protected $database; private $dbdriver; function __constru...

Should I use sessions for "LOGINS" on my site?

I have a classifieds website, where anyone (no need for login currently) can post a classified. It is PHP based. The procedure for posting is currently like this: click on "New Classified" ---> fill in a form of all information and hit "View classified before publishing it" ---> the form submits to a "verify classifieds" page, where...

How can I add a Boolean field to MySQL DB?

It seams I should use tinyin(); but I dont know how to implement it? The question is what is your recommendation if I need to have a boolean field in MySQL DB and modify it´s value with PHP. Thanks in advance! Trufa ...

Zend Db Table Three tables JOIN

Hello. I have 3 tables. 1. payment(user_id, calculation_id) 2. user(id, user_name) 3. calculation(id, period_start_date, period_end_date) I need to select payments with user_name, period_start_date, period_end_date. How can I do it within one query in Zend Framework? Thanks a lot. ...

MySQL Pass table name to cursor select

I want the procedure to take parameter answertable and partid in the select statement, but when i call it it doesn't replace the parameter answertable with the value the call call updateTotalScores('quiz_participation', 'quiz_answer', 1) returns the error: 1146 - Table 'quizdb.answertable' doesn't exist passing the id works, but passi...

MySQL query optimization is driving me nuts! Almost same, but horribly different

I have the following two queries (*), which only differ in the field being restricted in the WHERE clause (name1 vs name2): SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num FROM first A JOIN second B ON A.third_id = B.third_id WHERE A.name1 LIKE 'term%' SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num FROM first A JOIN second...

Why do I get a "using password: YES" error even when I don't give the -pXXXX parameter?

I'm getting this error when softweb:~ # mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I'm not even inputting the password and its telling me that I'm giving one. in the my.cnf file there is no login saved. If I change the password to the one I had before I can log in without writing -pXXXX ...

How do I set up an index on a pair of columns so that the values are tied together?

I have a table with id, external_id and country_code columns. I have two rules which I want the database to impose: Each External ID can only appear once per country code Each ID can only appear with at most one non-null External ID, and vice versa. The first rule is easy enough - I add a unique multi-column index to external_id and...

Sync large local DB with server DB (MySQL)

I need to weekly sync a large (3GB+ / 40+ tables) local MySQL database to a server database. The two databases are exactly the same. The local DB is constantly updated and every week or so the server DB need to be updated with the local data. You can call it 'mirrored DB' or 'master/master' but I'm not sure if this is correct. Right now...