Linking tables
Hi all, having a spot of bother with a 'keywords' table. I have a table of articles with an article_id and a table of categories with a category_id, all is well. These are linked via a article_link table so that an article can be a member of multiple categories. I then have a table of 'keywords' that will relate to each article. I wish to be able to SELECT all articles associated with the keywords. No issues so far. Next, I wish to be able to dynamically add a table to the database, eg: a 'cats' table. If a cat has a keyword of 'tiddles' associated with it. I would then like to be able to SELECT all related articles, that is, all records in the articles table, with the keyword 'tiddles'. MySQL 5 and the db layout looks like this so far. thanks for looking Kevin CREATE table articles ( article_id int(9) NOT NULL auto_increment, user_id int(9) default NULL, article_category_id int(11) NOT NULL, article_title varchar(50) NOT NULL, article_subject varchar(50) NOT NULL, article_blurb varchar(250) default NULL, article_body text, article_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, article_publish_date timestamp NOT NULL default '-00-00 00:00:00', article_orig_author varchar(50) NOT NULL, article_image longblob, PRIMARY KEY (article_id) ); CREATE table article_categories ( article_category_id int(11) NOT NULL auto_increment, article_category_name varchar(20) NOT NULL, article_category_left_node int(11) NOT NULL, arcitle_category_right_node int(11) NOT NULL, PRIMARY KEY (article_category_id) ); CREATE table article_links( article_link_id int(11) NOT NULL auto_increment, article_id int(11) NOT NULL, article_category int(11) NOT NULL, PRIMARY KEY (article_link_id) ); CREATE table keywords( keyword_id int(11) NOT NULL auto_increment, keyword_name char(20) NOT NULL, PRIMARY KEY (keyword_id) ); -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
modify default value
Hi, I am mass-importing A LOT of csv tables. In order to distinguish between files I change the default value for the table before doing a load data infile.. from a Perl script: ALTER TABLE `data` MODIFY COLUMN `sample_id` INTEGER UNSIGNED NOT NULL DEFAULT $sample_id[$idx] After importing a few hundred files I now realize that the table is copied each time the alter table statement is executed. Is there a way to avoid this? I think there at least should be a way since nothing in the structure is altered. Please advise! Thanks, Bernd
Re: modify default value
Yes, use ALTER TABLE tbl ALTER COLUMN col SET DEFAULT 'foo'; and you won't rebuild the whole table. You know, you can also just manually specify a value for that column in the LOAD DATA INFILE statement. I think you can anyway -- I always have to check the syntax on that one. Baron Bernd Jagla wrote: Hi, I am mass-importing A LOT of csv tables. In order to distinguish between files I change the default value for the table before doing a load data infile.. from a Perl script: ALTER TABLE `data` MODIFY COLUMN `sample_id` INTEGER UNSIGNED NOT NULL DEFAULT $sample_id[$idx] After importing a few hundred files I now realize that the table is copied each time the alter table statement is executed. Is there a way to avoid this? I think there at least should be a way since nothing in the structure is altered. Please advise! Thanks, Bernd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't fetch result twice in a PHP script
I am using mysql 4.1.11 and PHP 5
In one of my php scripts I make 2 successive calls of mysql_fetch_row using
the same Mysql Result.
In the first call the desired result is achieved, but in the second one it
isn't.
I have something like
while($someArr=mysql_fetch_row($result))
{
...some code.
}
The first time I call mysql_fetch_row , the code inside the while loop gets
executed, but not the second time. What is the reason behind that.
--
-Mahmoud Badreddine
http://www.spreadfirefox.com/
Re: Can't fetch result twice in a PHP script
At 16:35 -0500 15/9/07, Mahmoud Badreddine wrote:
In one of my php scripts I make 2 successive calls of mysql_fetch_row using
the same Mysql Result.
In the first call the desired result is achieved, but in the second one it
isn't.
I have something like
while($someArr=mysql_fetch_row($result))
{
...some code.
}
The first time I call mysql_fetch_row , the code inside the while loop gets
executed, but not the second time. What is the reason behind that.
Sounds more like a PHP question really, but have you thought about
trying mysql_fetch_array() instead? I can't see why it should be any
different, but it might be worth a shot - always works for me anyway.
:-)
But also: are you confident there's more than one row to be fetched?
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
He who slings mud, usually loses ground.
-- Adlai Stevenson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't fetch result twice in a PHP script
it is more of a PHP question , I admit.
I tried mysql_fetch_array, mysql_data_seek, mysql_result . Same result.
And I am sure there's more than zero rows to be fetched, because it works in
the first call, but not the second.
On 9/15/07, Chris Sansom [EMAIL PROTECTED] wrote:
At 16:35 -0500 15/9/07, Mahmoud Badreddine wrote:
In one of my php scripts I make 2 successive calls of mysql_fetch_row
using
the same Mysql Result.
In the first call the desired result is achieved, but in the second one
it
isn't.
I have something like
while($someArr=mysql_fetch_row($result))
{
...some code.
}
The first time I call mysql_fetch_row , the code inside the while loop
gets
executed, but not the second time. What is the reason behind that.
Sounds more like a PHP question really, but have you thought about
trying mysql_fetch_array() instead? I can't see why it should be any
different, but it might be worth a shot - always works for me anyway.
:-)
But also: are you confident there's more than one row to be fetched?
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
He who slings mud, usually loses ground.
-- Adlai Stevenson
--
-Mahmoud Badreddine
http://www.spreadfirefox.com/
Re: Can't fetch result twice in a PHP script
This one time, at band camp, Mahmoud Badreddine [EMAIL PROTECTED] wrote:
I have something like
while($someArr=mysql_fetch_row($result))
{
...some code.
}
The first time I call mysql_fetch_row , the code inside the while loop gets
executed, but not the second time. What is the reason behind that.
The array pointer is now at the end of the result set.
Kevin
--
Democracy is two wolves and a lamb voting on what to have for lunch.
Liberty is a well-armed lamb contesting the vote.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't fetch result twice in a PHP script
Mahmoud Badreddine wrote: it is more of a PHP question , I admit. I tried mysql_fetch_array, mysql_data_seek, mysql_result . Same result. And I am sure there's more than zero rows to be fetched, because it works in the first call, but not the second. That just means there is one row in the result and only one row. you should really print the query and then execute it using some other tool to see the result, I would be willing to bet there is only one row in the result. An error that has caught me more than once is having another query inside the look and using the same result variable. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling a second mysql binary with a distinct configuration.
I'm running mysql-5.0.46 on a linux system on a VPS. As such, the yum package
manager is being used to install a standardized mysql installation, and I due to
other system dependencies, I can't change this.
I need a second mysql installation on that machine that runs under a completely
different directory tree and is totally separate in all ways from the standard
installation. Therefore, I downloaded the source and compiled it to install
itself under the /usr/private directory tree. I did this as follows:
dir=/usr/private
./configure \
--prefix=${dir}\
--exec-prefix=${dir} \
--bindir=${dir}/bin\
--sbindir=${dir}/sbin \
--libexecdir=${dir}/libexec\
--datadir=${dir}/share \
--sysconfdir=${dir}/etc\
--sharedstatedir=${dir}/var\
--localstatedir=${dir}/var \
--libdir=${dir}/lib\
--includedir=${dir}/include\
--oldincludedir=${dir}/include \
--infodir=${dir}/info \
--mandir=${dir}/man
I did a make followed by a make install, and everything indeed got
installed under the /usr/private tree. However, the various programs
still seem to be looking by default in /etc for the my.cnf file. Since
/etc/my.cnf is the file that the standard version of mysql uses, this new,
private version tries to point to the directories that are specified in the
old version's configuration, which is clearly not what I want it to do.
I was hoping to get it to look for a second my.cnf file inside of
/usr/private/etc, but that isn't happening.
It seems like the build is ignoring the --sysconfdir parameter, at least
with regard to the location of the my.cnf file.
What am I doing wrong? Or is this some sort of deliberate feature of mysql?
Thanks in advance for any light you can shed on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
