Date: 01/30/07 (PHP Community)    Keywords: php, mysql, sql

I know this isn't a MySQL community, but I figured since PHP goes hand-in-hand with it, someone here could help me :)

I'm creating a tracking system for our labs that's recording login and computer information every time a user logs in. The schema is:

| tID         | int(10) unsigned
| tUsername   | varchar(32)      
| tHostname   | varchar(16)      
| tAgree      | tinyint(1)       
|tIp         | varchar(16)      
| tLoginTime  | datetime         
| tLogoutTime | datetime         

tid	tuser	thost	tagree	tip			tlogintime			tlogouttime		
256  	user1  	host79 	1  	123.213.289.244  		2007-01-25 06:44:11  	2007-01-25 10:11:09  
257 	user5 	host87 	1 	123.213.289.101 		2007-01-25 09:14:12 	2007-01-25 09:39:28 
258 	user3 	host59 	1 	123.213.289.180 		2007-01-25 09:27:37 	2007-01-25 10:11:37 
259 	user4 	host41 	1 	123.213.289.39 		2007-01-25 09:41:21 	2007-01-25 10:25:57 
260 	user2 	host65 	1 	123.213.289.55 		2007-01-25 10:05:45 	2007-01-25 10:21:36 

Source: http://community.livejournal.com/php/535296.html

Date: 01/30/07 (Asp Dot Net)    Keywords: asp, sql

Simple question: (I hope)

I have Visual Studio 2k5 and when I create a SQL datasource on a page (URLadd.aspx) when I point the conector at the sql server I am using I cannot use the advanced SQL generation util. Anyone any idea why?

Source: http://community.livejournal.com/aspdotnet/82505.html

Date: 02/05/07 (PHP Community)    Keywords: php, programming, css, html, xml, asp, sql, java, web, google

As I look for alternative employment I see a lot of wanted ads asking for a web designer who are proficient in several web-programming languages (they usually specify PHP/ASP/CGI-Perl), the use of several different databasing formats (Access, SQL of some sort, Oracle) - proficient in several different forms of design (Photoshop, Flash, Illustrator, FreeHand, Quark, etc) as well as being a master of JavaScript, XHTML, XML, DHTML, CSS and any other acronym they seem to have found on google.

To me this sounds like they are asking for a web-design team wrapped into one person. Am I crazy; or are they?

Source: http://community.livejournal.com/php/538219.html

Date: 02/07/07 (PHP Community)    Keywords: mysql, sql

Pre-question: what's a good method for displaying a script in livejournal?

Real question:
Below the cut is a very quick function I wrote up to query for the existence of a table in MySQL. I want to cut it down even further and I was thinking the logic :

start table exists
query `SHOW TABLES LIKE 'myTable';
if result && result num_rows > 0 then table exists?
return true
else
return false
end table exists

would be faster because it wouldn't loop. My thought is, the only way the query could return true(1+ results) is if it matched that table exactly (lack of %'s in the query expression). Anyone disagree?


public function tableExists($table) { $retVal = false; if(!$this->link_id) { return $retVal; } $result = $this->query('SHOW TABLES LIKE ?;',$table); if($result) { if($result->numRows() > 0) while($row = $result->fetchNumeric()) { if($row[0] === $table) { $retVal = true; //Shortcircuit the loop for speed. return $retVal; } } } return $retVal; }

Source: http://community.livejournal.com/php/539308.html

Date: 02/08/07 (Asp Dot Net)    Keywords: database, asp, sql

Ok, I'm pretty much at a loss here. I've got a database (SQL Server 2000) that I'm trying to connect to using an xsd DataSet using VS 2005. I've built it and it works for everything I've been trying to do so far in this application, and several others, until now. What I'm trying to do is do a very simple search inside one of the fields, so that my users can find all the names of the people whose names begin with a certain letter. The commandText I created for the method is simple:

SELECT     dbo.Personal.*
FROM         dbo.Personal
WHERE LastName LIKE @SearchText

Pretty simple, I thought. The code I use to bind the results to a data grid is:

gvListing.DataSource = tblPersonal.GetDataByLastName(txtSearchText.Text & "%")

Now, the "interesting" things begin. When I search for a user's last name using only the first letter, I don't get any rows returns. However, if I do a search for something like "S%%%", it will return all the names in the database that have 5 characters in them.

I was under the impression that the % was the zero or more wildcard for SQL Server databases, but this is making me thing that ASP.NET is translating it as a single character wildcard. I've also tried using * instead of %, but it never returns a result, same with ?. I'm at a loss here, so any insight on how to get this simple search to work would be wonderful/

Source: http://community.livejournal.com/aspdotnet/83748.html

Date: 02/11/07 (WebDesign)    Keywords: php, mysql, sql, web, linux, apache

I hate to be a bit obtuse. However, I wanted to gather people's thoughts on using Linux as a web development/design platform.

Are there any users out there that have used Linux and/or Windows in this capacity?

If so, what web dev/design tools for Linux do you find helpful and/or comparable to Windows-based applications (outside of GIMP, Apache, MySQL, LDAP and PHP-Eclipse, of course)?

Finally, if your user requirements for a website gave you the choice of either Mono or PHP, which would you pick and why?

Thanks in advance!

*cross-posted to linux*

Source: http://community.livejournal.com/webdesign/1220790.html

Date: 02/14/07 (PHP Community)    Keywords: mysql, sql

I have a puzzling problem. I have this function below, used to find an author's numeric id from the author's name. It makes several checks, changing to string for alternate spellings to find a match. The problem is that if it finds a match in the first pass, it returns the number, but if it finds a match in subsequent loops, it doesn't (I had it echoing the information to check) and I don't know why!

I call it with $author_id = get_author($author, 0);

function get_author($author, $loop) {
global $authors_table;
$max = 5;

switch($loop) {
case 0:
$search_author = $author;
break;
case 1:
$search_author = ucfirst($author);
break;
case 2:
$search_author = strtolower($author);
break;
case 3:
$search_author = str_replace("-", " ", $author);
break;
}

$query_author = "SELECT id FROM $authors_table WHERE author = '$search_author'";
$result_author = mysql_query($query_author) or exit(mysql_error());
$count = mysql_num_rows($result_author);

if ($count == 1) {
$author_data = mysql_fetch_assoc($result_author);
return $author_data['id'];
} elseif ($count == 0 AND $loop < $max) {
$new_loop = $loop + 1;
get_author($author, $new_loop);
} else {
return false;
}
}

Does anyone have any idea? Thanks!

Source: http://community.livejournal.com/php/540825.html

Date: 02/17/07 (PHP Community)    Keywords: php, mysql, sql, java, web

Hello again,

I have a few small projects I work on in PHP--mostly personal things and occasionally a project or two for work.

The problem is that I often work on more than one machine: one for work and my laptop. The projects rarely mingle, but I frequently find the need to refer to my old projects.

I'm basically looking for a cvs-like system in PHP. Like a personal online code repository, if you will. I've been running a MediaWiki installation, but it's really not designed for this sort of thing.

Ideally, it would have basic change tracking, search, and syntax highlighting (although if it doesn't, I'm handy enough with highlight_string() to add it in :). Bonus points for built-in debugging (yeah right) and for other language support (Java/C/MATLAB/TeX a plus). I'm not concerned at all about collaboration features, but I wouldn't mind them.

Any suggestions? I have PHP5/Rails/Python/MySQL available. I would prefer something in PHP/MySQL as I'm more comfortable with that pairing, but the others definitely aren't foreign.

(I'd like to keep this web-based and leave command-line utilities (e.g. cvs and subversion) as last resorts.)

I suppose I could write my own, but I guess that kind of brings about the whole "chicken and egg" thing.


Thanks in advance, everyone. Have a great long weekend!


edit: a friend just told me about PEAR, and I'm looking into it. Opinions?

Source: http://community.livejournal.com/php/541813.html

Date: 02/17/07 (PHP Community)    Keywords: php, mysql, browser, css, html, database, sql, web, linux, apache

Schedge, an Austin based company, is reinventing scheduling.

We are looking for an experienced Lead PHP developer.

Have you developed enterprise scale PHP applications?
Have you worked with technologies like AJAX and MVC?

Then this might be the position for you!

You'll be responsible for leading the development efforts of the core application. The position is contract-to-hire with the option of equity in the company.

Key Areas of Responsibility:
* PHP Application and database development
* Work with Designers and other Developers to build complex user interfaces and data interactions
* Write/Update functional specs

Required Experience:
* Enterprise scale PHP experience
* MVC (Model-view-controller) design experience
* Basic DOM Scripting experience
* Basic (X)HTML & CSS experience
* MySQL
* Multi-browser development
* Apache Webserver

Preferred additional experience:
* Experience with the CakePHP (or similar) PHP framework
* Flash or Action Scripting experience
* MySQL clustering
* Code development for Linux servers

You'll be a good fit if:
* You “get the Web”, and understand it’s full potential.
* Work efficiently and get it right, not just "good enough"
* Can set and execute priorities individually and in a group
* Effectively communicate to people of varying levels of technical expertise

Please note: Telecommuting is possible for this position but the ideal candidate will be located in Austin, TX and available for weekly meetings.

If you feel you are a good fit for this position please send your resume and rate per hour to jobs@schedge.com

[Cross-Posted to several list] My apologies if you see it multiple times.

Source: http://community.livejournal.com/php/542006.html

Date: 02/17/07 (Web Development)    Keywords: php, mysql, browser, css, html, database, sql, web, linux, apache

Schedge, an Austin based company, is reinventing scheduling.

We are looking for an experienced Lead PHP developer.

Have you developed enterprise scale PHP applications?
Have you worked with technologies like AJAX and MVC?

Then this might be the position for you!

You'll be responsible for leading the development efforts of the core application. The position is contract-to-hire with the option of equity in the company.

Key Areas of Responsibility:
* PHP Application and database development
* Work with Designers and other Developers to build complex user interfaces and data interactions
* Write/Update functional specs

Required Experience:
* Enterprise scale PHP experience
* MVC (Model-view-controller) design experience
* Basic DOM Scripting experience
* Basic (X)HTML & CSS experience
* MySQL
* Multi-browser development
* Apache Webserver

Preferred additional experience:
* Experience with the CakePHP (or similar) PHP framework
* Flash or Action Scripting experience
* MySQL clustering
* Code development for Linux servers

You'll be a good fit if:
* You “get the Web”, and understand it’s full potential.
* Work efficiently and get it right, not just "good enough"
* Can set and execute priorities individually and in a group
* Effectively communicate to people of varying levels of technical expertise

Please note: Telecommuting is possible for this position but the ideal candidate will be located in Austin, TX and available for weekly meetings.

If you feel you are a good fit for this position please send your resume and rate per hour to jobs@schedge.com

[Cross-Posted to several list] My apologies if you see it multiple times.

Source: http://community.livejournal.com/webdev/387663.html

Date: 02/17/07 (PHP Development)    Keywords: php, mysql, browser, css, html, database, sql, web, linux, apache

Schedge, an Austin based company, is reinventing scheduling.

We are looking for an experienced Lead PHP developer.

Have you developed enterprise scale PHP applications?
Have you worked with technologies like AJAX and MVC?

Then this might be the position for you!

You'll be responsible for leading the development efforts of the core application. The position is contract-to-hire with the option of equity in the company.

Key Areas of Responsibility:
* PHP Application and database development
* Work with Designers and other Developers to build complex user interfaces and data interactions
* Write/Update functional specs

Required Experience:
* Enterprise scale PHP experience
* MVC (Model-view-controller) design experience
* Basic DOM Scripting experience
* Basic (X)HTML & CSS experience
* MySQL
* Multi-browser development
* Apache Webserver

Preferred additional experience:
* Experience with the CakePHP (or similar) PHP framework
* Flash or Action Scripting experience
* MySQL clustering
* Code development for Linux servers

You'll be a good fit if:
* You “get the Web”, and understand it’s full potential.
* Work efficiently and get it right, not just "good enough"
* Can set and execute priorities individually and in a group
* Effectively communicate to people of varying levels of technical expertise

Please note: Telecommuting is possible for this position but the ideal candidate will be located in Austin, TX and available for weekly meetings.

If you feel you are a good fit for this position please send your resume and rate per hour to jobs@schedge.com

[Cross-Posted to several list] My apologies if you see it multiple times.

Source: http://community.livejournal.com/php_dev/75751.html

Date: 02/20/07 (PHP Community)    Keywords: mysql, sql

I have some MySQL tables. The relevant ones are:

COUNTRIES:
id, country

PRODUCTS:
prodcode, prodname, catid

STOCK:
id, prodcode, countryid, stockamt, lastupdate

At the moment, I am using the following query:
SELECT `stock`.`stockamt`, `products`.`prodcode`, `countries`.`country` FROM `stock` LEFT JOIN `products` ON `stock`.`prodcode` = `products`.`prodcode` LEFT JOIN `countries` ON `stock`.`countryid` = `countries`.`id`

And getting the following results:

Product Code	Stock Amount	Country
AMA-N-SW	324	UK
AMA-N-SW	546	Australia
AMA-N-SW	11	USA
BOOBS	123	UK

However, I would like to get something more like:

	UK	Australia	USA
AMA-N-SW	324	546	11
BOOBS	11	0	0

I have tried all sorts of GROUP BY queries, UNIONs, inserting the result into a multidimensional array and piecing it back together again and I just cannot figure out for the life of me how to make this work. Should I be working on my MySQL query, my presentational code, should I be changing the structure of my DB? Any help you can give would be wonderful.. and please try and make it relatively idiot proof as I've been on this for about 4 hours and my brains hurt. Thanks :)

Update: based on the awesome help I got I've found this which seems to be the most friendly explanation of what I want to do. Far too tired to do it now though, so I'll update again tomorrow with my solution (for anyone that is interested) :)

Source: http://community.livejournal.com/php/543919.html

Date: 02/21/07 (MySQL Communtiy)    Keywords: mysql, sql

Does anyone have recommendations for a MySQL 4x - 5x book targeting intermediate to advanced users, perhaps some sort of book that talks about schema optimizations? The company I work for has been progressively reworking, over the last three years, the solution to handle multiple feeds of MLS/real estate information and I honestly got to say I am stumped as well. The problem is figuring out how to normalize several independantly designed schema feeds into one consolidated and hopefully unified source instead of multiple sub-tables that feed into one gigantic view.

Source: http://community.livejournal.com/mysql/109677.html

Date: 02/21/07 (IT Professionals)    Keywords: mysql, database, sql, postgresql, web, hosting

I used to work in web hosting and domain registration and the like. But I've been out of it for the last 2 and half years focusing on backup and disaster recovery. Anyway, does anyone know of a "good" hosting and domain registration service? I know from experience that a lot of these companies will milk you for every dollar they can and you need to have a company that is on the up and up. Has anyone had good experience with a particular company?
The site will be a small specialty site. No commerce or what have you, but we will need email, and about 10 MB of space. This will be a business but should not have a lot of traffic. 10 hits per day average. If possibly, I would like to have the ability to do database (MySQL or PostgreSQL) and shell access but that is not a requirement.

Thank you in advance for any help.

Source: http://community.livejournal.com/itprofessionals/52421.html

Date: 02/24/07 (PHP Community)    Keywords: mysql, sql, linux

I'm getting an puzzling error in a mysql query. The thing is that locally, on my Windows NT server, it works, but when I install the program on my host, which runs Linux, it says me the query is wrong.

The error message I'm getting is : "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 '(SELECT ncis_series_cats.series_id FROM ncis_series_cats WHERE"

And my query is:
SELECT ncis_chapters.story_id, ncis_stories.series_id, ncis_series.user_id, ncis_series.series_title, ncis_series.series_rating, ncis_series.series_summary, ncis_series.series_notes, DATE_FORMAT(ncis_series.series_date, '%d/%m/%Y') AS date_f, ncis_series.series_complete, ncis_users.name FROM ncis_chapters LEFT JOIN ncis_stories ON ncis_chapters.story_id = ncis_stories.id LEFT JOIN ncis_series ON ncis_series.id=ncis_stories.series_id LEFT JOIN ncis_users ON ncis_users.id=ncis_series.user_id WHERE ncis_stories.series_id = ANY (SELECT ncis_series_cats.series_id FROM ncis_series_cats WHERE ncis_series_cats.cat_id = '00012') ORDER BY ncis_chapters.date DESC LIMIT 10

I can't understand why it works under NT and it doesn't works under Linux. Does anyone have an idea? Many thanks in advance.

ETA: My host is running MySql 4.0.27, while I'm running 5.0.22.

Source: http://community.livejournal.com/php/545108.html

Date: 03/02/07 (IT Professionals)    Keywords: database, asp, sql, web, google

Hi! I'm currently making a website for a school project using ASP.NET and SQL and it has a registration form. How do I keep the registered entries unique like say...make sure no two people get the same login name? My 'loginname' variable in my database is data type nvarchar(50). I've tried google and some other sites but I don't think I'm getting the keywords correctly.

Thanks for your time!

Source: http://community.livejournal.com/itprofessionals/53657.html

Date: 03/04/07 (PHP Community)    Keywords: php, mysql, html, database, asp, sql, web, yahoo

Okay, I'm still getting the grasp of php... I went off for a while to actually learn html... Okay, well. I'm having the most troubles with mysql. It's quite confusing, along with cookies. Anyways, this isn't going to be about cookies. I want to just know how I can somehow get a nice list of... everything. Somehow. I really want know what's IN my mysql database and what names, etc. I'm using a pre-made script, so I'm rummaging around in circles with no straight answers on what what is.

Anyways, I'm going to go to sleep, tomorrow I'll check back for answers for this... I'd like to know if I need to make a page or something like... www.website.com/mysqltestpage.php and go there.


Eh. Anyways, I need a lot of advise, if someone has msn/yahoo/aim/irc/skype/etc. uhhh tell me if you'd let me add you and ask questions from time to time.

Source: http://community.livejournal.com/php/547218.html

Date: 03/05/07 (MySQL Communtiy)    Keywords: mysql, sql

Is there a built-in MySQL function for calculating Percentile?

Source: http://community.livejournal.com/mysql/110063.html

Date: 03/07/07 (PHP Community)    Keywords: php, mysql, css, html, database, sql, web, microsoft

I have to make an online volunteer hour logger for a school project, so I've been teaching myself PHP for the past few days, but for some reason none of the scripts I've tried have worked for me. I have a domain and server that provide MySQL and a web-based database manager (CPanel) and know HTML and CSS pretty well.

Basically what I want my website to be able to do is:

1. Enable users to register (fields: name, student no., password, password verification, email)
2. Login and submit their volunteering information in a form (fields: organization name, event/activity, volunteer type (drop down), date, #hours, reference letter (check box))
3. Be able to edit this information after submission
4. Logout
5. Possibly the administration being able to access a report summarizing a certain user's information? (like a query on Microsoft Access)

I've been following: http://www.tutorialized.com/tutorial/Signup-and-Login/3027 but after doing everything I don't know how to connect users to members-only pages.

My website right now is: http://www.rhscolts.org/cas
Login will be registration and login, and Your Hours will have a "Please login or register" message unless the person is already logged in, in which case the form and their volunteering info will appear.

Is this too hard to do? Because I asked some people before I set about doing this project and they told me all I need is a few scripts, but then nothing is really working right now :(

Can anyone help me please?
My email is sheatore@hotmail.com if you want to talk to me directly.
Please and thank you in advance, ANYONE!

Source: http://community.livejournal.com/php/548674.html

Date: 03/08/07 (MySQL Communtiy)    Keywords: mysql, sql

For those that follow Daylight Savings Time in the US and Canada, watch out this weekend, because we “spring forward”!

The biggest caveat I have is: Do not arrive 1 hour late to work on Sunday or Monday.

As for MySQL, to test if you are fine, run:

SELECT @@global.time_zone;

If you get back “SYSTEM”, then MySQL is looking to the OS for timezone data, which is the default.

The real sanity check:

SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00');

This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. If not, and you’ve played with timezone data, remember that timezone data is only loaded when MySQL starts, so if you haven’t restarted MySQL since you patched your OS, you need to do that.

This is mostly stolen from a MySQL list post I found here:

http://lists.mysql.com/mysql/205321

Source: http://community.livejournal.com/mysql/110281.html