Jump to content

Big_Pat

Members
  • Posts

    38
  • Joined

  • Last visited

Big_Pat's Achievements

Member

Member (2/5)

0

Reputation

  1. Maybe it's just the vagaries of Excel, then, but if I simply enter the CSV as prepared by Excel it reads the comma in the album's title as a separator. In other words, 1,Ever After,Iron Tongue,The Dogs Have Barked, the Birds Have Flown,Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013 it sees the comma, understandably enough - the album's title is actually 'The Dogs Have Barked, the Birds Have Flown'.
  2. Ah, that's it. Excel added the double quotes around fields it had escaped. This works: Thanks! 7,Said N Done,Iron Tongue,'The Dogs Have Barked\, the Birds Have Flown',Iron Tongue,04:57,2013,Southern Rock,-1,Neurot,US,,3219,2013
  3. Well, I tried your suggestion but to no avail. One line of the CSV file reads like this, when opened in a text editor: 1,Ever After,Iron Tongue,"The Dogs Have Barked, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013 and the result is that the title has split to two columns with no quotes and no comma. I then tried 1,Ever After,Iron Tongue,"The Dogs Have Barked\, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013 but the result was " "The Dogs Have Barked" in one column and " the Birds Have Flown" " in the next, with no comma or escape \.
  4. I might've solved this myself, actually. When loading the csv file back into Excel, the title entry after the escaped comma has moved to the next column which, of course, I don't want. Seems I need to prepare my CSV file better.
  5. The following code is supposed to take a CSV file and insert to the database. First, I prepare the CSV file by replacing " with \" and , with \, then run a PHP function to prepare for insertion. It's not working the way I want it to. I've tried reading about delimiters and escapes but I've not found a solution. Can you help, please? The excerpted code for the insertion is: if ($_FILES[csv][size] > 0) { $file = $_FILES[csv][tmp_name]; $handle = fopen($file, "r"); do { if ($data[0]) { mysql_query("INSERT INTO songlist (track_number,title, etc, etc) VALUES ( '" . mysql_prep($data[0]) . "', '" . mysql_prep($data[1]) . "', etc, etc ) "); } } while ($data = fgetcsv($handle, 1000, ",", "'")); and mysql_prep's code is: function mysql_prep($value) { $magic_quotes_active = get_magic_quotes_gpc(); $new_enough_php = function_exists("mysql_real_escape_string"); // i.e. PHP >= v4.3.0 if ($new_enough_php) { // PHP v4.3.0 or higher // undo any magic quote effects so mysql_real_escape_string can do the work if ($magic_quotes_active) { $value = stripslashes($value); } $value = mysql_real_escape_string($value); } else { // before PHP v4.3.0 // if magic quotes aren't already on then add slashes manually if (!$magic_quotes_active) { $value = addslashes($value); } // if magic quotes are active, then the slashes already exist } return $value; } Is there a better function available? At the moment, commas in titles are being read as end-of-entries, despite being escaped.
  6. Thank you, Barry, that's worked a charm. Why does yours work and mine doesn't?
  7. So what songs in my collection are cover versions of Bob Dylan songs? I wrote this query to find out: $query = "Select artist from songlist where title in (Select distinct title from songlist where artist='Bob Dylan')"; but running it times out. If I substitute the inner select for a simple list of, say, three or four of his songs, the query runs fine so I can only assume that the thousands of permutations are what's slowing things down. Is there a more efficient way of achieving the same thing? Ultimately I'd like to replace 'Bob Dylan' with a variable, but first things first. Thanks!
  8. I really like it. It's way beyond my skillset, for sure! If I could change anything, I'd have the speech bubble pointer in the 'What People Say?' section above the picture, or at least with a bigger gap between the arrow and the person. The poor chaps look like they're being struck by lightning...Also the navbar main <li> links (Tutors, Students, etc) don't go anywhere. I realise you're supposed to choose an item underneath them, rather than the buttons themselves, but perhaps a default value for the buttons might be an idea. I hope you don't mind me commenting, as I'm no expert - well, I'm no expert at php, but I AM an expert in browsing websites :-) Other than those, I think it's clean and neat. No problems in Chrome @ 1920x1080.
  9. My employers are definitely taking my php fiddling seriously. Having given them a nice call-stats page, they now want me to make an online manual for the Helpdesk staff to put on the company's intranet. My first thought was to just give them a nice CMS, maybe by putting TinyMCE inside some web design or other, and then letting them update it themselves. Then, though, I found phpDocumentor, and I figured that would look nicer. However, about an hour into it I have to say the documentation for it is...puzzling, shall we say. Has anyone here had any experience of using it? If it's not absolutely amazing, I'm thinking it's not worth the hassle of learning something else when I have a workable alternative. Any thoughts?
  10. Oh, that's good too! I'd have to put a few ORs in (the %, th\'%, a %, for example) but that'd work I reckon. Also, the music place has come back to me and told me how to put the 'prefix' column in. So many options now!
  11. Thanks, David, that's what I was thinking about when I mentioned the new column 'prefix' in my original post. For manageability, I didn't fancy running an update script every time I uploaded a new infile (although I suppose I could always code that into the input page) and I've asked the writers of my music program if there's a way to populate a custom field with a sortable version of title, artist and all. In the meantime, though, while I'm waiting for them, I've gone ahead and written a script that uses the expanded query I mentioned in my original post. It works fine, but the downside is that it has to grab EVERY artist before php filters by letter. It doesn't take long at all with 'only' 30,000 titles, but it wouldn't be very practical for hundreds of thousands. I appreciate everyone's help!
  12. Thanks for the replies. Dathremar, your idea works, but isn't quite what I was looking for. I would, as you say, ignore the 'The' bands when searching under T, but wouldn't show them up in, say 'R' for 'Ramones, The'. The database structure is pretty much dependent on a CSV file output from my music program so, without a lot of fiddling, it'll be difficult to do anything other than add a column for prefix or amend the php as stated above.
  13. Hi again, There's something bugging me about my music database, and that's listing bands under 'T' when their name starts with The. I'd like to write some code to change this, but my current queries wouldn't work without a bit of a reshuffle. Currently I'm using a select option for the user to select a letter of the alphabet like so: $query = "SELECT title, artist, album, album_artist from songlist"; if (isset($_POST["letter_filter"])) { $letter = $_POST["letter_filter"] . "%"; $query .= " WHERE title LIKE '$letter'"; } else { $query .= " WHERE title LIKE 'A%'"; } $query .= " order by title asc"; And that's fine, it works well. Of course, this way bands starting with 'A', 'The', or even solo artists will appear under the wrong letter - apart from The The I suppose! Now, I found this piece of code which works, but not with my current query. The code is: SELECT title, artist, album, album_artist,CASE WHEN SUBSTRING(`artist`,1,2) LIKE 'a ' THEN SUBSTRING(`artist`,3) WHEN SUBSTRING(`artist`,1,3) LIKE 'an ' THEN SUBSTRING(`artist`,4) WHEN SUBSTRING(`artist`,1,4) LIKE 'the ' THEN SUBSTRING(`artist`,5) ELSE `artist` END AS `artist_sort` FROM `songlist` ORDER BY `artist_sort` This would be fine if my initial query returned EVERY result but, as you can see, I currently only query by the first letter. I've got a couple of options, I think. One would be to make a new column in my MySQL database called 'prefix', and populate that with sorted artist names (Sex Pistols, The, for example) and use my existing queery or I could re-write my query to return ALL results using the code above and then apply a letter filter in PHP. Both seem a little wasteful of resource, but I can't think of another way. Which, do you think, would be the better of the two options?
  14. Thank you for the explanation. I'd seen the term singleton but wasn't sure what it referred to. I've amended my connection.php and it's definitely quicker than before. I still wouldn't say it was anywhere near as fast as MySQL but it's not awkwardly slow any more. I really appreciate the time taken by all here in replying.
  15. Thanks for the reply. I'd forgotten all about mysql_prep, actually (a little function that checks which version of PHP and magic_quotes, etc). I commented it out and re-tested, but it's still slow. Your second point, though, I think must be the reason. I am creating a NEW PDO and referring to that with my function call connect_db(). I'm afraid I know no other way - what do I do to call a NEW PDO the first time, but not subsequently?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.