Date: 12/12/05 (MySQL Communtiy)    Keywords: php, mysql, database, sql

I've just set up a testing server on a windows platform, it's not ideal but it's better than nothiing, for myself, when trying to create a table in a database, either manually or thru phpmyadmin, the MySQL server stops responding. I'm using the latest release of MySQL although that seems not to matter as it seemed to do it with an earlier version. Any ideas on this??

Source: http://www.livejournal.com/community/mysql/76242.html

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

I am trying to globally replace a character in mysql. Specifically, I would like to replace the string '#&39symbol#&39;' with the string '\'symbol\''. Al already know how to do the update/replace operation, but in this case that's not sufficient. The string could potentially appear in any field of any table, so it needs to be global. Does anyone know a way to do a global find and replace, or have a script already written to do this? I am willing to do it on my own if necessary, but I figure there's no point in reinventing the wheel.

Thanks,
Patrick

Source: http://www.livejournal.com/community/mysql/76327.html

Date: 12/13/05 (PHP Community)    Keywords: mysql, sql

I am not very experienced at writing queries that pull from several tables...

Can anyone tell me why $maillist has 639 results when it should have only 8 (where assgo.aprivi='m') ?

code

$maillist = array();

$Link = mysql_connect ($Host, $User, $Password);

$Query = "SELECT fb_user.emailAddress, fb_user.ustatus, passgo.aprivi, member_services.salesAlert 
FROM fb_user, passgo, member_services 
WHERE fb_user.ustatus='active' AND passgo.aprivi='m' AND member_services.salesAlert='y' 
";

$Result = mysql_db_query ($DBName, $Query, $Link);

if (!$Result) {
print("$Query
\n");
echo 'MySQL Error: ' . mysql_error();
exit;
}



while($Row = mysql_fetch_array ($Result)) {
$email = $Row['emailAddress'];
$type  = $Row['aprivi'];
$send  = $Row['salesAlert'];

$maillist[] =  array($email, $type, $send);
}
mysql_close ($Link);

$Query = "SELECT 
u.userID, 
u.emailAddress, 
ui.cats, 
p.aprivi, 
ms.salesAlert 
FROM fb_user AS u 
INNER JOIN passgo AS p ON u.userID = p.goID 
INNER JOIN member_services AS ms ON u.userID = ms.serviceID 
LEFT JOIN fb_user_interest AS ui ON u.userID = ui.userID 
WHERE u.ustatus='active' 
AND p.aprivi='m' 
AND ms.salesAlert='y'";

Source: http://www.livejournal.com/community/php/378930.html

Date: 12/14/05 (Web Development)    Keywords: php, mysql, database, sql, web, apache

I was wondering if anyone could recommend me a webhost that will meet the following requirements:

+ I want as much control as possible. If I decide one week I want to mess around with OracleDB, I want to be able to install it. If I want to use Ruby on Rails, I want to be able to install that too. I basically want to be able to do as much server administration as possible

+ I have the following musts: PHP 5, Apache, MySQL(preferred, but some kind of database)

+ I need a decent amount of space and bandwidth, but not a whole lot.

I have considered a dedicated server, but it seems to be far too expensive for now at least. Any ideas?

Source: http://www.livejournal.com/community/webdev/279180.html

Date: 12/14/05 (MySQL Communtiy)    Keywords: php, mysql, sql

SQL Query:
----------
SELECT grps.groupid, grps.title, grps.description, grps.approved, grps.create_date, grps.leaderid, user.username AS leadername, grps_categories.catid, grps_categories.title AS catname, grps_setting.image_name, grps_setting.private_posts, grps_setting.moderate_members, grps_setting.hidden_group, grps_setting.edit_details
FROM grps_setting
LEFT JOIN user ON (user.userid = grps.leaderid)
LEFT JOIN grps ON (grps.groupid = grps_setting.groupid)
LEFT JOIN grps_categories ON (grps_categories.catid = grps.catid)
WHERE grps.groupid = 1;


Error Message:
--------------
MySQL Error : Cross dependency found in OUTER JOIN. Examine your ON conditions
Error Number : 1120


My Question:
------------
where's the cross dependency and why does this cause the query to malfunction?

x-posted to php_mysql

Source: http://www.livejournal.com/community/mysql/76794.html

Date: 12/14/05 (Javascript Community)    Keywords: php, mysql, database, sql, java

Hi, I have some questions regarding the best way to go about creating dynamic dropdowns in javascript.

I have to create a form with two dropdown lists. One for US states the other for counties within those states. The county dropdown will remain blank until the user selects a state, then the county dropdown will be populated with the correct counties for that state.

I have this working using PHP (the rest of the application is PHP) but I want to do it without reloading the page. The list of states and counties are stored in 2 tables in a mysql database.

My first instinct is use PHP to write the javascript for me. Do the DB work with PHP and then write the javascript arrays. But that seems wrong.

I would think that the javascript should go something like this and would be called from an onchange handler in the state dropdown. I dont know the exact syntax.

create function setOptions(id)

create array of counties indexed with their id's from the database

if there is an id passed to the function
    iterate through the array of counties
        if a key matches the id then write that to the options tag of the county select list

Source: http://www.livejournal.com/community/javascript/88041.html

Date: 12/16/05 (MySQL Communtiy)    Keywords: mysql, sql

This is bound to be something simple I've overlooked (and besides, I'm new at this)...

First, the table. Created from a pre-existing spreadsheet for a community library so please forgive the non-optimum design...


mysql> create table books (
-> book_id mediumint unsigned not null auto_increment,
-> author1 varchar(255) not null,
-> author2 varchar(255) not null,
-> title varchar(255) not null,
-> subtitle varchar(255) not null,
-> subject1 varchar(255) not null,
-> subject2 varchar(255) not null,
-> subject3 varchar(255) not null,
-> subject4 varchar(255) not null,
-> publisher varchar(255) not null,
-> year smallint unsigned not null,
-> loc varchar(255) not null,
-> edition varchar(255) not null,
-> isbn varchar(255) not null,
-> location varchar(255) not null,
-> notes varchar(255) not null,
-> PRIMARY KEY (book_id)
-> );
Query OK, 0 rows affected (0.01 sec)


Then a data import (load data local infile "/home/lev/Desktop/test.csv" into table books;) - no problem.

Then adding a fairly large fulltext index i.e.,

mysql> alter table books add fulltext index (author1, author2, title, subtitle, subject1, subject2, subject3, subject4, publisher, location);

But a search, any search e.g.,

mysql> select * from books where match (author1,author2) against ('ali');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

help?

Source: http://www.livejournal.com/community/mysql/77082.html

Date: 12/18/05 (PHP Community)    Keywords: cms, php, mysql, sql

How do you make your site searches? I am constructing (yet another) CMS of late and am once again at the issue of searching. I am trying to build an SQL statement that will do a full text search of looking in several fields and then ranks each return so they can then be ordered by that before looping them out in php.

How do other people do this? have you found a particular way of doing this that you find works well. My current solution isn’t the most efficient method out there.

Thanks

X-posted to mysql

Source: http://www.livejournal.com/community/php/380708.html

Date: 12/22/05 (PHP Community)    Keywords: php, mysql, html, sql, web

I am learning classes (trying to anyway).

I have class called photo. It contains two functions:

create_folder()
sterilize_text()

In the create_folder function I want to clean user uploaded data before creating a folder based on that data. I have a few special rules so I wanted to put this in a seperate function instead of regexing all over my pages.

I read that using functions within functions in classes is easy, but I don't know how and the tutorial I'm using doesn't touch on using nested functions.

So what happens is when I invoke the class and use create_function(), php exits with this:

Fatal error: Call to undefined function: sterilize_text() in /var/www/html/includes/classes.php on line 34
Line 34 is : $brand = sterilize_text($myrow[brand]);

Is there a way to code this so I can use functions within functions, inside of my class? I've included the relevant scripts below.

Thank you.

class photo
{

	var	$model;

	// mk the folder for an uploaded image.
	function create_folder($model)
	{

		global $dbx, $dbxold;
		if (!isnum($model)||strlen($model)>5) {return(false);}
		
		$q = "SELECT brand FROM spec WHERE model='$model'";
		$result = mysql_query($q,$dbxold);
		$myrow = mysql_fetch_array($result);

		$brand = sterilize_text($myrow[brand]); 

		if ($brand=="") {return(false);}
		$webpath = "/images/dirtbikes/$brand";
		$uploaddir = $_SERVER["DOCUMENT_ROOT"] . $webpath;
		if (!file_exists($uploaddir)) {
			mkdir ($uploaddir, 0777);
			chmod($uploaddir,0777);
			if (!file_exists($uploaddir)) {
				echo "
unable to create directory. :(
";
				return(false);
			}
		}

		$dir_array[] = $webpath;
		$dir_array[] = $uploaddir;
		return $dir_array;

	}

	function sterilize_text($text)
	{
		$hyphen = 	"_";
		$dash = 	"-";
		$text = strtolower($text);
		$text = ereg_replace('[^a-zA-Z0-9_\\,]', '', $text);
		$text = ereg_replace('[\\]', $dash, $text);
		$text = ereg_replace('[,]', $hyphen, $text);
		return $text;
	}

}

create_folder($model); 

	echo "
";
	print_r($dir_array);
	echo "
";

?>

Source: http://www.livejournal.com/community/php/382729.html

Date: 12/23/05 (PHP Community)    Keywords: php, mysql, rss, database, sql

Hiya,

I don't have a shell account and uploads via PHP are disabled on my server and this is why I'm looking for a robust RSS parser which feeds into a MySQL database. Since it's nearly impossible to install anything I'm looking for functions/classes I can use.

I have been able to get LastRSS to work but this does not use MySQL and writes to a directory. I need to leave that directory set to 777 - which is, clearly, no good.

I've lost faith in the RSS parser I wrote. It glitches too often.

Does anyone have any recommendations?

Source: http://www.livejournal.com/community/php/383211.html

Date: 12/28/05 (PHP Community)    Keywords: php, mysql, sql, web

I'm the webmaster for Devo bootleg archive Booji Boy's Basement, and I'm converting the whole thing to a PHP/MySQL based back end. However, I've run into a bit of a wall trying to figure out how to implement something.

The DVD pages contain thumbnail screenshot images which link to a full-size screenshot. Here's an example. (Thumbnails are at the bottom.) Some DVD have only 3 shots, some as many as 12. I'd like it to be any number (within reason). However, I've no bloody clue about what would be the best way to implement this in PHP and MySQL.

So, if anyone can give me suggestions and guidelines, I'd appreciate it. What I'm looking at is a second table just for the screenshots, each with an identifier linking it to an entry in the DVD table, but I hope there's a simpler way.

Source: http://www.livejournal.com/community/php/383988.html

Date: 12/28/05 (MySQL Communtiy)    Keywords: mysql, database, sql

I am trying to make it so this result can be ordered by the last name of the client. However, the name is in another table thus the JOIN LEFT. That part seems to be working OK (I think), but where I am running into a problem is that the results are showing duplicates (example if invoice 10, there are 3 result rows for invoice 10, but only 1 row in the actual MySQL table "invoice_main").

(Yes, there are some name/case mismatches, but I didnt create the database.)

Thanks!

SELECT DISTINCT
invoice_main.invoice_number,
invoice_main.invoice_date,
invoice_main.due_date,
invoice_main.clientid,
invoice_main.subtotal,
invoice_main.paid_amounts,
invoice_main.payment_status,
clients.ClientLast
FROM $TableName
LEFT JOIN clients ON invoice_main.clientid=clients.ClientID
WHERE invoice_main.office_id='$office_id'
ORDER BY $SortParam
LIMIT $limit,$Limit_increment

Adding GROUP BY invoice_main.invoice_number gave me an SQL error


[UPDATE] Figured it out
LEFT JOIN clients ON invoice_main.office_id=clients.office_id AND invoice_main.clientid=clients.ClientID

Gotta thank jedifreeman and his help with JOIN a couple weeks ago. I was re-reading our AIM conversation and thought of this.

Source: http://www.livejournal.com/community/mysql/80119.html

Date: 12/31/05 (PHP Community)    Keywords: php, mysql, html, sql

Ok, I'm being slowly driven nuts by this. There is a syntax error and whatever it is, my eyes are just sliding right past it each time. If anyone can spot it I will be eternally grateful. The error is "
Parse error: parse error, unexpected ';' in /home/remove/public_html/test/root/admin_functions.php on line 35"

I have marked the line 35 in red.

If anyone can think of a better way of doing this (while your here) that would be great. It takes tags much like you would set for an LJ entry, splits them up and then checks the tags table for them, if they aren’t there it then adds them. Then for each tag, it sets up a map in the maptag table which links the page_id to the tag id (the tag id in the tags table is set in an auto_increment, so it have to run yet another query to find this out).

It doesn’t have to be too fast though, its only admin.

function update_tags($page_id, $tags_string)
{
$tag_array = explode(',', $tags_string, -1);
$count = count($tag_array);
for ($i = 0; $i < $count; $i++)
{
$update_tags_query = "SELECT `id` FROM `tags` WHERE `name` = '" . $tag_array[$i] . "'";
$update_tags_result = mysql_query($update_tags_query) or die('update tags query failed: ' . mysql_error());
if(mysql_num_rows($update_tags_result)==0)
{
//add entry to tags table
$tag_update_query = "INSERT INTO tags (name) VALUES ('" . $tag_array[$i] . "')";
mysql_query($tag_update_query) or die('tag update query failed: ' . mysql_error());

//then find out the new tags id
$tags_query = "SELECT `id` FROM `tags` WHERE `name` = '" . $tag_array[$i] . "'";
$tags_result = mysql_query($tags_query) or die('tags query failed: ' . mysql_error());
$tags_array = mysql_fetch_array($tags_result);

//then add to the maptag table
$maptag_update_query = "INSERT INTO maptag (contentid, tagid) VALUES (" . $page_id . ", " . $tags_array['id'] . ")";
mysql_query($maptag_update_query) or die ('maptag update query 1 failed: ' . mysql_error());
}else(
//add entries to the maptag table
$update_tags_array = mysql_fetch_array($update_tags_result);
$maptag_update_query = "INSERT INTO maptag (contentid, tagid) VALUES (" . $page_id . ", " . $update_tags_array['id'] . ")";
mysql_query($maptag_update_query) or die ('maptag update query 2 failed: ' . mysql_error());
}
}
}

Source: http://www.livejournal.com/community/php/386426.html

Date: 01/01/06 (PHP Community)    Keywords: php, mysql, html, sql, web

Hi all, I'm a complete and total noob to PHP and MySQL. I'm working on putting up a site/cart for a friend.

http://www.allthingsally.com

I'm barely dipping my toes in, I know, I'm using a pre-made cart, that self-installs, has a web-based back-end built in, and should be easier than pie.

I've managed to get much of the changes made from the demo cart that I wanted to, and now I'm stuck. The pages are all basically built off of a single template, and it's an HTML template at that, so it should be more in my realm of expertise. Now as you can see on the page, there is a graphic/logo that should be displaying on the top, but for the life of me, I can't get it to work. I've stared at it and relinked for about 4 hours now, and I'm getting no-where. Any help/ideas would be swell.

If this is too vague, or doesn't make any sense, just let me know and I'll elaborate any way I can. I'm sorry if this is beyond stupid.

Thanks in advance.

Source: http://www.livejournal.com/community/php/386583.html

Date: 01/01/06 (MySQL Communtiy)    Keywords: php, mysql, sql

Ok, new problem.

I have got my fulltext searches to work, but am very much against the idea of stopwords. many of them I want people to be able to search.

Now I have tried setting the path of the file containing them to nothing, but my shell access is having an off day (plus I don’t really want to change thing in there).

Is there a way in the query of letting some of the stopwords pass. I’m using version 4.0.25

Thanks for any help

x posted to php (because they use mysql too)

Source: http://www.livejournal.com/community/mysql/80433.html

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

Ok, new problem.

I have got my fulltext searches to work, but am very much against the idea of stopwords. many of them I want people to be able to search.

Now I have tried setting the path of the file containing them to nothing, but my shell access is having an off day (plus I don’t really want to change thing in there).

Is there a way in the query of letting some of the stopwords pass. I’m using version 4.0.25

Thanks for any help

(i know this is a php group, but not everyone here is part of mysql, but most of you seem to know your way round it (plus, this is for a php search page :D))

x posted to mysql

Source: http://www.livejournal.com/community/php/387303.html

Date: 01/03/06 (PHP Community)    Keywords: mysql, database, sql

While trying to connect to the mysql database, I get this error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
So I look up the solutions for this error. I discover that I need to edit a my.cnf file and change the socket setting. I wish it was that easy. I check what socket is set too.

[client]
#password = my_password
port = 3306
socket = /opt/local/var/run/mysqld/mysqld.sock

Not the socket the server is trying to connect to.

The file is located in /opt/local/etc/mysql4 and I also copied it to /opt/local/etc incase.
The password and username is correct, I can manually connect to the database.
Using a Mac OSX.4 server.
DB Server version is 4.1.15
Connection: Localhost via UNIX socket

I am out of ideas, can anyone help me?

Source: http://www.livejournal.com/community/php/388151.html

Date: 01/03/06 (MySQL Communtiy)    Keywords: mysql, database, sql

While trying to connect to the mysql database, I get this error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
So I look up the solutions for this error. I discover that I need to edit a my.cnf file and change the socket setting. I wish it was that easy. I check what socket is set too.

[client]
#password = my_password
port = 3306
socket = /opt/local/var/run/mysqld/mysqld.sock

Not the socket the server is trying to connect to.

The file is located in /opt/local/etc/mysql4 and I also copied it to /opt/local/etc incase.
The password and username is correct, I can manually connect to the database.
Using a Mac OSX.4 server.
DB Server version is 4.1.15
Connection: Localhost via UNIX socket

I am out of ideas, can anyone help me?

Source: http://www.livejournal.com/community/mysql/80709.html

Date: 01/05/06 (PHP Community)    Keywords: php, mysql, sql

I've tried several variations of code.
(even direct copy from several sites)

But I can't seem to delete a table from my php file.
(works great from directy sql in phpmyadmin)

Here is the code I am working with (connection already made to db)

Any ideas? Thanks!

-----

$tblname = "tablename";

$result = @mysql_query("DROP TABLE '$tblname'");
if (!$result)
{
echo ("Error--Unable to Delete Table. Try Again Later");
exit;
}

Source: http://www.livejournal.com/community/php/389160.html

Date: 01/06/06 (PHP Community)    Keywords: php, mysql, html, sql, web

So what is the best way to serve documents (DOC PDF Images, other…) from a location outside of your public html directory?

I thought about using mysql and the LONGBLOB attribute.

But surely there must be a way to simply store the files and use php to retrieve them????
(I’ve done more web searching than I care to admit)

Source: http://www.livejournal.com/community/php/390051.html