I am building a blog archive navigation menu. Currently I run a query to get all the years and months. Then I loop through and run the following query to get all id's and titles of that year/months blog posts: SELECT `id`, `title` FROM `news` WHERE YEAR(`date`) = "2010" AND MONTH(`date`) = "03" ORDER BY `date` DESC After that, to...
Hello! I need your help for the following problem: These are my tables bonusCodes (called bc from now on) id code users maxinvoices maxusers activefrom activeuntil forfatter dato usedBonusCodes (called ubc from now on) id codeid (henviser til bonusCodes.id) fakturaid forfatter dato question what I want is the query to...
I used an online SQL builder to help design some MySQL tables I'm working on. Foreign keys always confuse me. The code I came up with tries to add these 4 foreign keys but I want to make sure that I want them before I add them. ALTER TABLE `users` ADD FOREIGN KEY (user_id) REFERENCES `settings` (`user_id`); ALTER TABLE `logins` ADD ...
Hi I have a table with feilds : file_id, rating, user_id There is one rating per user_id, so there could be many rating (in scale of 0-5) for a single file_id. I want to find avg of ratings for every file_id and then display 5 file_id with highest avg rating. Actually my sql query looks like: SELECT m.server_domain, m.original_...
I can get all the tables containing column 'hostname' using: select select table_name from information_schema.columns where column_name='hostname'; If I knew the names of all the tables I could use a union like: SELECT * FROM ((SELECT hostname FROM table1) UNION (SELECT hostname FROM table2) ... UNION (SELECT hostname ...
Hello guys, I am trying to anchor an echo value. My code is as follows; while ($row = mysql_fetch_assoc($result)){ echo $row(['links']; } I would like to anchor the value of echo $row['links'] in HTML. Thanks guys. ...
Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output. Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL... So to try and explain: I have three tab...
My sql query looks like this: SELECT m.original_name, m.type, m.title, m.views, m.description, m.hash, AVG(mr.rating_scale5) as avg_rating_scale5 FROM c7_media m, c7_media_ratings mr WHERE m.public=1 AND m.hash = mr.media_hash GROUP BY mr.media_hash Here I am taking average of mr.rating_sacle5 grouped by mr.media_hash I want ...
Hi all, I tried to dump a database: <?php $dbhost = "localhost"; $dbuser = "XXXX"; $dbpass = "XXXXXXXX"; $dbname = 'testdb'; $list = shell_exec ("C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump.exe $dbname --user=$dbuser--password=$dbpass > dumpfile.sql"); ?> I tried both specified full path to mysqldump.exe or just use mysqldump, it s...
Hi, I tried to create a batch file that can backup all my databases. My systems details: OS: Server -> Windows Server 2003, Testing/local machine -> Windows Vista Databases: MySql 5.XX Batch file: @echo off START C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump.exe --opt -h localhost -uroot -psecret testdb | gzip > dump.sql"); In my co...
I'm trying to count how many times a certain article has been graded for example how many times have users_articles_id 3 been graded by my members. I'm also trying to count the points for a certain article for example users_articles_id 3 is related to the ratings database by its ratings_id the rating points should be a total of 13. I...
mysql> desc test; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | a | int(10) unsigned | NO | | NUL...
I am building a little search function for my site. I am taking my user's query, stemming the keywords and then running a fulltext MySQL search against the stemmed keywords. The problem is that MySQL is treating the stems as literal. Here is the process that is happening: user searches for a word like "baseballs" my stemming algorithm...
hi, i have installed vs2010 beta2, create a MVC website, i want to use subsoinc access a mysql database in SimpleRepository, when run the website, i get error: "Unable to find the requested .Net Framework Data Provider. It may not be installed. ". var repo = new SimpleRepository("NorthwindMySql", SimpleRepositoryOptions.None); var user...
Here are my failed attempts to include a PHP variable in a MySQL expression. Replacing the variable with a 1 results in the results being printed. Any help will be appreciated. $query = " SELECT name FROM teams WHERE id = '$shooterID'"; $shooters = mysql_query($query) or die(mysql_error()); $i = 0; while($shooter = mysql_fetch_arr...
I want to have a column in a database that can contain multiple entries. Is it possible to have to define the type of the column as an array (fixed-sized array or some dynamic collection) so that it can store multiple entries. ...
I was hoping to change one of the variables of a query, from inside the while loop, is it possible to do this? EG. $query = mysql_query('SELECT column1, column2 FROM table1 WHERE column1 = "'.$variable.'";', $conn); while ($data = mysql_fetch_assoc($query)) { if($data['column2'] == 'original') { $variable = 'altered'; } ...
I'm trying to change my rating system which only used one table before but now I'm changing it to use multiple tables and I really dont no how to update or insert a new rating into the database and was wondering how to do this using my MySQL tables structure? Also how do I do this by adapting the new code to my current PHP code which I...
I have a dataset with 100 000 datapoints which I have to plot on a graph. The resulting graph will be about 500px wide, so for every pixel there will be about 200 datapoints, which seems quite unnecessary. I need to find a way to get rid of the excess datapoints without losing the shape of the graph to speed up the rendering. Currently ...
When someone visits my website, he will see the page with the fewest pageviews. All pages, including a pageview counter, are stored in a MYSQL database. If a page is viewed, the pageview counter is increased by one. I now run into a Racing condition if my webserver has more than one thread: Thread 1: User A retrieves page P with pa...