-
Syntax for multiple left joins
Date: 03/12/06 (MySQL Communtiy) Keywords: database
I'm working on a database to organize fan fiction stories. My current problem is figuring out how to generate a list of all the pairings that exist in the database for a given fandom. The relevant tables and columns are these:
table story: storyid
table fandom: storyid | showid
table pairing: storyid | pairid
table pairchar: pairid | charid
table person: charid | charname
Each story has one or more pairings. Each pairing has one or more (1-4 in practice) associated characters. Characters can belong to more than one pairing within a story, and multiple stories may have pairings that involve the same set of characters.
I need results like this:
CharA | CharB | CharC | CharD
Sara | NULL | NULL | NULL
Grissom | Sara | NULL | NULL
Sara | Catherine | NULL | NULL
Grissom | Sara | Catherine | NULL
Grissom | Catherine | Nick | Warrick
I know that getting the names in a consistent order is just a matter of comparing character ids and SELECT DISTINCT will condense the list. What I can't figure out is the correct syntax for what I believe should be a series of left joins to generate the list in the first place. Suggestions? -
arrays
Date: 03/12/06 (PHP Community) Keywords: mysql, database, sql
Hi,
I'm sure there's a better way than the one I'm using to do what I want to do.
I'm trying to select items from a database and order them by "category", which is one of the columns of the table I use.
Then, I want the page to display the items with the category as a title, appearing only once.
Hopefully someone can help me improve my code :)
Here's what the table looks like :
____________________________
| category | year | title | stuff |
------------------------------------------
| theatre | year1 | title1| ... |
-----------------------------------------
| movie | year2 | title2 | ... |
Here's what I want :
Here's the code I use :$categorie = mysql_query("SELECT categorie FROM $nom",$connexion) or die(mysql_error()); $nb = mysql_num_rows($categorie); for ($i = 0; $i<$nb; $i++) { $perf = mysql_result($categorie,$i); $item[] = $perf; } $reduc = array_unique($item); foreach($reduc as $genre) { echo ""; }
Any help would be greatly appreciated ! -
finding and replacing a string in a database entry
Date: 03/20/06 (PHP Community) Keywords: php, database
I'd like to find and change the word "images/" in a database entry, and replace it with "http://mydomain.net/images/". Is there some way to do that with PHP, without having to update anything in the database?
I've tried it with preg_match(), but that didn't do anything. I'm still PHP noob anyways ^^ -
DB_DataObject and OOP
Date: 03/22/06 (PHP Community) Keywords: php, html, database, asp, sql
Ok - I'm going to try my best to not sound like I have no idea what I'm talking about -
I'm trying to move more towards pure OOP practices in my PHP projects, and I've been sifting through a couple of packaged MVC frameworks and some OnLAMP tutorials on writing you're own... very cool stuff, too bad i'm only like 3 years behind...
anyway - I use DB_DataObject primarily for my database interactions (if you don't know DB_DataObject is an object relational database tool with an automatic class generating script) - I _would_ like to be able to do some things easier/better with DB_DataObject (PK+FK JOINs in particular could be a bit more elegant) but overall it's a useful tool that cuts out a lot of SQL coding...
So - my actual question - in the article I'm digging thru (http://www.onlamp.com/pub/a/php/2005/09/15/mvc_intro.html) the author is extending his base framework class with a framework DB class that provides the database connection (it's basically a tiny class that sets $this->db) - I'm wondering what is the best way to incorporate DB_DataObject into a solid OOP class schema
- should I use the DataObject generated class as my base class that I extend - then what if I'm writing a class that doesn't need DB connectivity? The other thing I was thinking was to attach it to my other classes like $this->DO = new DataObject_Class but I really like having my class properties line up with my database fields (user.email = $user->email _not_ $user->DO->email); Is there something I'd be better off using the DB_DataObject (I can't bear the thought of -gasp- writing SQL anymore)
I'm somewhat new to this OOP game so any help is appreciated -
DataGrid Madness
Date: 03/23/06 (C Sharp) Keywords: programming, database
Hello everyone:
I'm curious if anyone has had in experiance with programming datagrids.
I'm using "C#" and what I want to do is build a report writer.
I populated a Datagrid from an Access database Table, now I want
build controls that let user select what columns to put into their report
and how to filter the rows based on the columns they've chosen.
Ie.
From a listbox you select Account# and ContactName.
The Datagrid shows the full range of Account#'s and ContactName's.
I want to give them a chance to filter the above datagrid by the rows they chose, in this case
Account# and ContactName.
I've devised that I want to try and build a 'Select' and refill and bind the dataset.
I'm just unsure about how to set up the sorting/filtering part. *head scratch*
Any help would be greatly appreciated! -
recursive query
Date: 03/23/06 (MySQL Communtiy) Keywords: php, database, sql
wondering if anyone could help me, i'm trying to help a friend quickly on a project, and i'm terrible at recursive functions
i have a table of 'genres' for music club 'venues' - each genre can have a 'parentID' if it is a sub-genre of another genre
really roughly he has something like this -
genre
-----
[genreID]
name
parentID
venue
-----
[venueID]
name
venure_genre
------------
[venueID
genreID]
I'm trying to help him build a form to submit venues into a database and assign them 1 or more genres -
i'd like to have the genre selection be a multiple select box, with subgenres spaced over with 's i.e.
I don't really need any help with the output as much as the query - I could do this the long way, and query all the top level genres, store them, then query each top level's sub genre's, etc etc, but i was wondering if anyone with experience writing recursive SQL querys/PHP functions could lend a hand
thanks
cross-posted to!['[info]']('http://stat.livejournal.com/img/community.gif')
php -
recursive query
Date: 03/23/06 (PHP Community) Keywords: php, mysql, database, sql
wondering if anyone could help me, i'm trying to help a friend quickly on a project, and i'm terrible at recursive functions
i have a table of 'genres' for music club 'venues' - each genre can have a 'parentID' if it is a sub-genre of another genre
really roughly he has something like this -
genre
-----
[genreID]
name
parentID
venue
-----
[venueID]
name
venure_genre
------------
[venueID
genreID]
I'm trying to help him build a form to submit venues into a database and assign them 1 or more genres -
i'd like to have the genre selection be a multiple select box, with subgenres spaced over with 's i.e.
I don't really need any help with the output as much as the query - I could do this the long way, and query all the top level genres, store them, then query each top level's sub genre's, etc etc, but i was wondering if anyone with experience writing recursive SQL querys/PHP functions could lend a hand
thanks
cross-posted tomysql -
Request for help
Date: 03/25/06 (PHP Community) Keywords: mysql, database, sql, web
Hi All..
I'm working on a script that will allow admins on my website to update events.
Basically what will happen (if I can get this to work) is the user will use some checkboxes on
page1 that will submit the id#'s to page2. I'm streaming the id#'s into page2 as an array, where the information will be edited, and then submitted as an UPDATE statement to the database in page3 (I've confirmed the values are transmitting)
The problem is on page 2, where the data should get called, I'm getting an error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
For simplicities sake, I've
I've put the relevant code up at http://pastebin.com/622150 (Livejounal tends to eat code)
If someone could take a look and tell me what I'm doing wrong or point me in the wrong direction then I'd be very appreciative. -
Encryption questions....
Date: 03/27/06 (Algorithms) Keywords: software, database, asp, security, web
Hello
I am a software engineer in MA for a small internet company. Currently I am working on a webservices API our product and have been struggling with the authentication model. I read around and found an article that talked about WSSE authentication This seems relatively easy to implement and I kind of have a mock demo set up, but there is a problem with my demo, that I am not sure how to fix, as I am not a cryptologist and though I use crypt() and know how to compare a plain text password to a crypt encrypted password, more advance topics are beyond me. So this is my problem I will refer to the ideas in the article so I recommend you giving it a quick read.
In the artcle it discusses creating a "password digest" using a "Created Date" a "nonce" and the "password string". as a Base64 encoded sha1 string(i'll probably ise md5). the sha1 string is "nonce"+"created date"+"password string". They then pass the nonce and create date in the header and assume that you have the password on the other end and can piece it back together creating another sha1 string to compare it too and verify authentication.
I have a test ap, and here is the problem problem I am running into. Say I have my api, and I have a company writing an app to use it. I tell them to use the above method and to use crypt to create their password string from their user inputed password. I get their data parse the headers and have the 3 aspects. I decode the base64 string to the sha1 string, but when I compare them it fails. The problem being that they are not encrypted with the same salt when crypt was used. Therefore the encrypted password they put in their string is different than the encrypted version in my database. This can be fixed if they know my salt, but that's a security risk. So I am not sure how to get around this problem.
Suggestions?Source: http://community.livejournal.com/algorithms/74647.html
-
Encryption questions....
Date: 03/27/06 (Web Development) Keywords: database, asp, security, web
Currently I am working on a webservices API our product at work and have been struggling with the authentication model. I read around and found an article that talked about WSSE authentication This seems relatively easy to implement and I kind of have a mock demo set up, but there is a problem with my demo, that I am not sure how to fix, as I am not a cryptologist and though I use crypt() and know how to compare a plain text password to a crypt encrypted password, more advance topics are beyond me. So this is my problem I will refer to the ideas in the article so I recommend you giving it a quick read.
In the artcle it discusses creating a "password digest" using a "Created Date" a "nonce" and the "password string". as a Base64 encoded sha1 string(i'll probably ise md5). the sha1 string is "nonce"+"created date"+"password string". They then pass the nonce and create date in the header and assume that you have the password on the other end and can piece it back together creating another sha1 string to compare it too and verify authentication.
I have a test ap, and here is the problem problem I am running into. Say I have my api, and I have a company writing an app to use it. I tell them to use the above method and to use crypt to create their password string from their user inputed password. I get their data parse the headers and have the 3 aspects. I decode the base64 string to the sha1 string, but when I compare them it fails. The problem being that they are not encrypted with the same salt when crypt was used. Therefore the encrypted password they put in their string is different than the encrypted version in my database. This can be fixed if they know my salt, but that's a security risk. So I am not sure how to get around this problem.
Suggestions? -
general site setup questions
Date: 03/28/06 (WebDesign) Keywords: php, mysql, database, sql, web, linux, hosting
okay, so now that i've been picking all your brains, reading all these books, and checking out all these tutorials, last night i finally said to myself, "self, i need a god damn website."
fast forward a few hours and now i registered a site and have some hosting thanks to godaddy. i have linux hosting because it said that's what is needed for php (correct?), but when I try to load my test page (which includes a .inc file), i get:
Warning: main(/include/header.inc): failed to open stream: No such file or directory in /home/content/blahblahblah/1index.php on line 1
now i tried to see if the file is where i put it (website.com/include/header.inc) but, for some reason, it loads as text. meanwhile, when i try to view a .inc file online from the site i've been maintaining for work, it asks if i want to download it. this gives me the impression that it's uploading wrong, which confuses me since i'm using Fetch both times without tweaking any settings.
is this a case of me just trying to jump the gun and it's possible that while the ftp stuff is up and running, some other "stuff" needs to finish getting installed by them? also, it says i can have up to 15 (or 10, i forget) MySQL databases, yet damned if i know what i'll be needing them for. is that needed for include files to work, or is that for other stuff i may need?
tried checking out tutorials but none really answer these kinds of questions, and i want to get a vague idea of what i should be asking godaddy customer support before i just sound like i'm trying to rush things too fast.
edit: okay, now i'm confused. if i reference "/images/image.gif", so root-relative linking works in that case. yet, root-relative linking DOESN'T work for the include file. things work fine if i just make it a link (ie: include/header.inc as opposed to /include/header.inc). pretty obnoxious... how can i fix this?Source: http://community.livejournal.com/webdesign/1086868.html
-
Access Microsoft Access Database From Java Using JDBC-ODBC Bridge - Sample Code
Date: 03/28/06 (Java Web) Keywords: database, java
Previously I had provided the gist of how to access MS Access database from JDBC using JDBC-ODBC bridge. Today I will elaborate on that and provide you with two sample classes. The first - AccessJDBCUtil.java defines a method which provides you with a connection to the ms access database. The second - AccessJDBCTest.java uses the [...]
-
cross posted to webdev...
Date: 04/03/06 (PHP Community) Keywords: php, mysql, database, asp, sql, linux, hosting
I want to ask you guys out there for some suggegtions/advice on hosting companies. I have very specific needs:
I need a host that:
1) Is reliable and has good, quick customer service
2) Supports PhP on a windows server - I need to run ASP and PhP
3) Provides at least one SQL Server not mySQL) Database if not more
4) Lets me run my own mail server (not sure if this is done through hosting or domain registration)
5) Preferably lets me add sub-domains for little to no extra cash
Right now I have Enterhost and they are awful (don't support PHP, charge out the ass, their logs don't work, you can't have sub-domains or dedicated IP's and every time I ask them for something they say no). I'm looking at maybe HostExcellence? I need something with practically no downtime, as it is for a money-making site.
On the same subject, is it possible to move files over to the new host and access it through only the IP address before switching my domain name servers? I want to get everything moved and tested on the new server and make sure all the DB calls work before it goes live. Not sure if I could do that without hostin a second, 'testing only' domain temporarily, as I've never done a large-scale transfer like this.
Any advice (besides 'Just switch to a Linux server') is helpful. -
Another one of those hosting questions...
Date: 04/03/06 (Web Development) Keywords: php, mysql, database, asp, sql, linux, hosting
OK, so I know this is a weekly occurance here, but I want to ask for some suggegtions/advice on hosting companies. I have very specific needs:
I need a host that:
1) Is reliable and has good, quick customer service
2) Supports PhP on a windows server - I need to run ASP and PhP
3) Provides at least one SQL Server not mySQL) Database if not more
4) Lets me run my own mail server (not sure if this is done through hosting or domain registration)
5) Preferably lets me add sub-domains for little to no extra cash
Right now I have Enterhost and they are awful (don't support PHP, charge out the ass, their logs don't work, you can't have sub-domains or dedicated IP's and every time I ask them for something they say no). I'm looking at maybe HostExcellence? I need something with practically no downtime, as it is for a money-making site.
On the same subject, is it possible to move files over to the new host and access it through only the IP address before switching my domain name servers? I want to get everything moved and tested on the new server and make sure all the DB calls work before it goes live. Not sure if I could do that without hostin a second, 'testing only' domain temporarily, as I've never done a large-scale transfer like this.
Any advice (besides 'Just switch to a Linux server') is helpful. -
AJAX, SQL and PHP question
Date: 04/05/06 (PHP Community) Keywords: php, xml, database, asp, sql, java
Hi guys,
I was just fooling around with AJAX for the first time and it's working fine so far, but of course some problems are around.
My question is, is there some possibility to insert PHP code in Javascript?
I have my JavaScript file, and now I need to tell it somehow which ID to select from the database.
function showFan(str) {
var url="getFans.php?id=" + Math.random() + "&q=" + str
xmlHttp=GetXmlHttpObject(stateChanged)
xmlHttp.open("GET", url , true)
xmlHttp.send(null)
}
The second line is the one that bothers me. There should be the SQL table variable after the id=, like this; getFans.php?id=$country. The +Math.random() stuff is from the tutorial, no idea what it does, and the rest of the line is also from the tutorial, they used ASP there but I have no clue about asp. So can anyone help me out with and and tell me what PHP code I need to have in my getFans.php in order to make this work? My getFans.php is just a file with the usual database connections and printing stuff, nothing else.
Thanks is advance :) -
converting database from PostgreSQL to MySQL
Date: 04/05/06 (MySQL Communtiy) Keywords: mysql, database, sql, postgresql
Hello all.
Sorry if this question is offtop question.
I have dump of PostgreSQL database and now I want to use it with MySQL.
Can You help me with converting it?
Does anyone now how to do it? Or I should do it by hand?
Thanks. -
Getting data from SQL Server
Date: 04/06/06 (Asp Dot Net) Keywords: database, sql
We're transitioning from one content manager to another adn I've been assigned to figure out how the database in teh old CM worked. Looking through I see that it has binary data stored in the database. Thing is though I don't know how to actually extract the binary data and view it. I was looking online for information and it seems very complex and/or convoluted. Does anyone know of any good tutorials or methods of doing this? Its SQL Server 2000 SP3.
Of course if they just gave me access to the source code my life would be 10X easier, but apparently they want me to make this into a "learning experience."
Any help is greatly appreciated.Source: http://community.livejournal.com/aspdotnet/60435.html
-
PHP + PDO = Crazy Delicious?
Date: 04/06/06 (PHP Community) Keywords: database, sql
I'm having a weird problem with PDO on Fedora Core 5. I'm trying to connect to a sqlite database using PDO and it's just bombing out. I have a sqlite2 database, but apparently FC5 only supports sqlite3 databases (via PDO). So the following code just stops working. No error, no output, just fails silently.
$dbh = new PDO("sqlite:$db_path");
Now granted it SHOULDN'T work because it's a sqlite2 database and the sqlite: command is for sqlite3 databases, shouldn't it give me an error about that? Alternately is there a way to tell what version a sqlite database is that's on the disk? Like if it's a sqlite2 database use this command instead:$dbh = new PDO("sqlite2:$db_path");
That way my code can be intelligent enough to know what type of DB it is? -
Boston MySQL Meetup (Cambridge) on Monday, April 10th, 7 pm -- Performance Tuning!
Date: 04/07/06 (MySQL Communtiy) Keywords: mysql, database, sql
Jay Pipes (co-author of Pro MySQL) is in town and will speak about "MySQL Performance Tuning Best Practices". This is the workshop he'll be giving at the MySQL Users Conference, so if you can't go don't miss this meetup! There will be FREE pizza and soda.
We will have giveaways of Pro Mysql and gift certificates for free Apress books, and other swag like T-shirts, buttons, etc. RSVP for a headcount of soda and pizza at http://mysql.meetup.com/137/events/4875276/ (you will have to register; sorry.... :( )
Description of the workshop: Learn where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the "low hanging fruit" on the tree of bottlenecks. It's not rocket science, but with a bit of acquired skill and experience, and of course good habits, you too can do this magic! Jay Pipes is MySQL's Community Relations Manager for North America.
We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking -- you can park in ANY MIT lot after 3 pm, even if it says "parking by permit only". We are in building E51, room 372.
If you join the meetup, you'll get these messages automatically and great things like 30% discounts on O'reilly books, discounts to conferences, etc. It's OK to repost/forward this message.
Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg ?selection=E51&Buildings=g o
This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg ?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)
Here are the URL's for the parking lots:
http://whereis.mit.edu/map-jpg ?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg ?selection=P5&Parking=go
Pizza and soda will be served, so please RSVP accurately.
When:
Monday, April 10, 2006, 7:00 PM 2006-04-10 07:00:00
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117 -
MySQL fills Oracle-consumed hole in database
Date: 04/07/06 (Application Development) Keywords: database
Open-source database company, with its own storage engine in the works, renews contract with Oracle around InnoDB.
| Previous page | || | Next page |