mysql

MySQL Certification Practice Questions

i am wondering how should i assume what these questions are actually asking? part of the question Clients without projects should be displayed, but projects that don't have a start date and projects that don't have an end date should not be displayed. answer mysql> SELECT -> c.name AS CLIENT, p.name AS PROJECT, -> p.st...

Installing PHP in IIS 5.1

I know there have been some similar questions to this, but they haven't helped me. I'm trying to install Wordpress 2.8.4 on my XP box, with IIS 5.1. I installed MySQL, and found that it was possible to install Wordpress through the Microsoft Web Platform Installer. After installing Wordpress (the install worked fine), I proceeded to the...

SQL query to extract projects that user has access to

I have a project table with projectId as primary key: **projectId**, projectName, dateCreated, etc. Then, I have a userAccess table using a composite primary key: **userId**, **projectId** Only the users listed in the userAccess table for each project will be able to view the projects. Now, I am trying to write a MySQL query whic...

Mysql, NOT EXISTS, SELECT

Well, I was trying to select rows from one table if there are no rows in another table. My original query was: SELECT * FROM `jos_datsogallery` as a WHERE a.published = 1 and a.approved=1 NOT EXISTS (SELECT * FROM `jos_datsogallery_votes` As v WHERE v.vip=62 AND v.vpic=a.id) ORDER BY a.imgdate DESC but it keeps failing. I made some...

Multi-tier applications with PHP?

Hi all, I am relatively new to PHP, but experienced Java programmer in complex enterprise environments with SOA architecture and multitier applications. There, we'd normally implement business applications with business logic on the middle tier. I am programming an alternative currency system, which should be easy deployable and custom...

What is the most efficent way of updating an "order" column in MySQL via PHP.

I'm currently creating a Flash CMS via Flex and the Zend Framework. In the Flex interface, I have a list of drag-able items pulled from a MySQL database via the Zend Framework. The user can rearrange the items by dragging and dropping them which will change their order in the mapped Flex array. Back on the server I have a column in my ...

Streamlining Database/Login Process with PHP/MySQL

Hello all, I'm currently working on a small webapp where I work. I've created this for different departments. So, for example, let's say that: Marketing, Human Resources, and Sales uses it. Right now, as it stands, I have 3 completely different directories for each dept: http://mydomain.com/hr http://mydomain.com/marketing http://...

read/write unicode data in MySql

I am using MySql DB and want to be able to read & write unicode data values. For example, French/Greek/Hebrew values. My client program is C# (.NET framework 3.5). How do i configure my DB to allow unicode? and how do I use C# to read/write values as unicode from MySql? Upddate: 7 Sep. 09 OK, So my Schema, Table & columns are set to...

php wordpress query

I had posted this earlier on Stack Overflow, but couldn't get a positive result. I thought I should do this again. <?php require_once 'news/wp-config.php'; $howMany = 0; $query ="SELECT `ID`, `post_title`,'post_category', `guid`,SUBSTRING_INDEX(`post_content`, ' ', 100) AS `post_excerpt` FROM $wpdb->posts WHERE `post_status`= \"publish\...

Visually Display Database Information with PHP/MySQL

Hi, I'm looking for a way to visually display my users' (schedules) at a glance. This is what my tables essentially look like: users: usrID usrFirst usrLast etc.. 22 John Doe semesters: id name year 1 Spring 2009 class_info: id name building ro...

how to use a like with a join in sql?

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A 'is like' a column from B meaning that anything can come before or after the column in B: for example: if the column in A is 'foo'. Then the join would match if column in B is either: 'fooblah', 'somethingfo...

MySQL delete from Multiple tables using ON not working

Hey everyone I am trying to delete from multiple tables with the following query mysql> DELETE -> info, pagelets, shingles, links -> FROM -> info -> INNER JOIN pagelets -> ON info.page_key=144 -> AND info.page_key=pagelets.page_key -> IN...

How to insert the value derived from AUTO_INCREMENT into another column in the same INSERT query?

I have an id column which is a primary key with AUTO_INCREMENT. I need the value that is generated to be inserted into the id column, as well as another column (which isn't set to AUTO_INCREMENT, and isnt unique. Currently I use the mysqld_isnert_id() function to get the id, and simply run an update query after the insert, but I was wo...

Automatic decimal number formatting in SQL or PHP?

In my DB, most numeric values are of type DECIMAL(10,6). They are currency columns. Now, when I retrieve the numbers from mysql, I get something like this 34.123456 //I want these 0.987654 500.000000 //I do not want these 1.000000 Is it possible to have the numbers automatically show up as integers when they are and to maintain deci...

MYSQL: Retrieve latest unique records

I have a table that logs rows with some duplicate data. The column fields are as follows: id: primary key, auto inc. member_id: int index item_qt: int id, member_id, item_qt, timestamp 553, 107, 10, time 552, 107, 25, time 551, 122, 6, time 550, 122, 9, time 549, 107, 19, time I would like to do a few thing...

difference of date in sql and php

how can calculate (now) - (date in database) ...

Can you use index in mysql using "col1 OR col2"?

I have a mysql query that gets a list of private messages where a user is either the sender, or receiver. SELECT users_user1.user_name AS pm_username_1, users_user1.user_avatar AS pm_username_1_avatar, users_user2.user_name AS pm_username_2, users_user2.user_avatar AS pm_username_2_avatar, pms.* FROM pm pms L...

Adding an If/else error/success message to PHP MYSQL function

Im wondering how i could add a success or fail message to return at the end of this function function InsertIntoDB($field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8, $field9){ $sql = "INSERT INTO `caches` ( `url` , `username` , `password` , `cachename` , `lat` , `long` , `message` , `notes` , `tags` ) VALUE...

How can I abstract mysqli prepared statements in PHP?

I'm using my own class for database queries, extending mysqli: class iDatabase extends mysqli { public $errorMsg; private $totalQueries; private $stmt; public function __construct() { parent::__construct( 'localhost', 'asd', 'asd', 'asd' ); if ( mysqli_connect_errno() ) { $this->errorMsg = 'Could not connect to server.<...

updating a table by joining multiple tables

I have the following query SELECT e.topicShortName, d.catalogFileID, e.topicID FROM catalog_topics a LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID LEFT JOIN catalog_lu_topics e ON a.topicID = e.t...