PhpMyAdmin Headaches 1

[ZOR]

I put the question on the MySql forum but did not get far. This is a real headache. I have entered 40 records into MySql using import from a file from Access. Using PhpMyAdmin. I keep having problems selecting some records past record 30 for editing. I get a query appear instead of the record :

This is what I see when I select the records not shown on page 2 for editing:

MySQL returned an empty result set (i.e. zero rows).SQL query:SELECT *
FROM `template`
WHERE `ID` =37
AND CONVERT( `Location`
USING utf8 ) = ''
AND CONVERT( `OrderNumber`
USING utf8 ) = ''
AND CONVERT( `PWRD`
USING utf8 ) = ''
AND `OrderDate` IS NULL
AND `Group` =5
AND CONVERT( `Description`
USING utf8 ) = 'SX321 LARGE'
AND CONVERT( `FujiPartNumber`
USING utf8 ) = 'P10WSXL124A'
AND CONVERT( `Duration`
USING utf8 ) = '124 mins'
AND CONCAT( `UnitPrice` ) = 7.94
AND `Qty` =0
AND CONCAT( `OrdSubTotal` ) IS NULL
AND CONCAT( `RunningTotal` ) IS NULL
AND CONVERT( `Title`
USING utf8 ) = '';

All I am trying to do is put something in the Title field. I have re-installed PhpMyAdmin / MySql several times. The installation is a package from XAMPP. I have also tried installing WAMP5, that produced the same results. Any ideas would be very greatfull, losing so much time on this. Thanks

 

[jpadie]

i find the question a little hard to follow.

i would expect your query to report only one row (if any) as it appears you are searching on a primary key.

"putting something into the title field": should you not be using an insert query?

 

[ZOR]

Thanks, I will try and explain. I have 40 records in the database. I am using PhpMyAdmin to go into the database to edit some records. I go into browse, and it lists 1-30 records. I can go into any one of these records individualy, or check all, and edit records in columnar layout. If I go into page 2, where records 31-40 are shown in browse, I cannot select record 37 or 40 for editing, as I get the page appearing in my last message. Somebody thought it was a collation problem. At the moment I think its Latin1_General, although the query above seems to use utf8. I tried setting all to utf8 the last time but it made no difference. Its strange as always record 37 is one of the offending records. I don't know why it displays the query mentioning conver this/that. All I'm trying to do is edit a record. Thanks

 

[jpadie]

in the qry above you are searching on pwrd but from yur post in the mysql forum, this field does not exist in the template table. i would expect the query to fail whenever you run it.

the query above is a select qry and, of course, has nothing to do with an action (replace or update or delete or insert)

 

[ZOR]

Thanks. PWRD at present is just an empty field. I don't know why this select query gets generated on records it won't let me go into. All records are virtually identicle. Its basically a table structure that I am building as I go for later Webpage/PHP connection.

 

[ZOR]

Forgot to add, the query was since changed to include PWRD. Am I using PhpMyadmin correctly by going into records to change default values. When the import query is first run I can freely go into records without problems and then it fails. Failure to go into records always happens with records 31+. I am concerned if I have these problems now then its going to get worse later. Are you only allowed to run queries on records in PhpMyadmin?

 

[jpadie]

phpmyadmin is an extremeley mature platform used by hundreds of thousands of people. i just cant believe that you have discovered such a major bug. occam's razor suggests that whatever you are facing is pure pilot error.

there is still a lot of your post on this thread that i dont understand. however you can run queries on a database anyway you like. yu are not restricted to using phpmyadmin, which is just a php front-end.

i have built your tables on one of my servers and am not able to replicate the behaviour you report although, as said, i dont truly understand your posts.

 

[ZOR]

I will try and explain again.
I start by importing a table from Access using a dump file which I import using PhpMyadmin. There are 40 records imported in the table. I then decide I want to chage some of the records values (eg.Description field) I select the table, select browse, click on the pencil icon (edit) of a record. It opens up, I make the change, its done. To get to records 31-30, I have to select page 2, or use the advance button. After a while I find certain records (past record 30), suddenly throw up the query shown earlier, and I cannot get to the record to make changes anymore. Thats as clear as I can explain the problem, I know I am not doing anything outside of the table definitions. I ask again, am I permmited to use PhpMyadmin to make changes to individual records the way I am doing it, ie select with pencil icon individually on a record or group. Thanks for your patience. Can you explain what parts you cannot understand in my posting and I will go over it again. Regards

 

[ZOR]

I dumped my table. I imported it again. I went into all records and could edit them. I then went in and edited every records "Price" field, cam out. When I went back to try an edit agin, the same two records 37 & 40 threw up query pages with nowhere left to go.

MySQL returned an empty result set (i.e. zero rows).SQL query:SELECT *
FROM `template`
WHERE `ID` =37
AND CONVERT( `Location`
USING utf8 ) = ''
AND CONVERT( `OrderNumber`
USING utf8 ) = ''
AND CONVERT( `Caption`
USING utf8 ) = ''
AND `OrderDate` IS NULL
AND `Group` =5
AND CONVERT( `Description`
USING utf8 ) = 'SX321 LARGE'
AND CONVERT( `FujiPartNumber`
USING utf8 ) = 'P10WSXL124A'
AND CONVERT( `Duration`
USING utf8 ) = '124 mins'
AND CONCAT( `UnitPrice` ) = 7.94
AND `Qty` =0
AND CONCAT( `OrdSubTotal` ) = 0.00
AND CONCAT( `RunningTotal` ) = 0.00;


MySQL returned an empty result set (i.e. zero rows).SQL query:SELECT *
FROM `template`
WHERE `ID` =37
AND CONVERT( `Location`
USING utf8 ) = ''
AND CONVERT( `OrderNumber`
USING utf8 ) = ''
AND CONVERT( `Caption`
USING utf8 ) = ''
AND `OrderDate` IS NULL
AND `Group` =5
AND CONVERT( `Description`
USING utf8 ) = 'SX321 LARGE'
AND CONVERT( `FujiPartNumber`
USING utf8 ) = 'P10WSXL124A'
AND CONVERT( `Duration`
USING utf8 ) = '124 mins'
AND CONCAT( `UnitPrice` ) = 7.94
AND `Qty` =0
AND CONCAT( `OrdSubTotal` ) = 0.00
AND CONCAT( `RunningTotal` ) = 0.00;

Why do these queries appear? How does the CONVERT bit come into things. Consistantly records 37 and 40 crash. If I try deleting them it comes up with a panel Do you really want to delete ....... I say okay and the record does not get deleted. I am just getting nowhere. I have searched XAMPP site for the problem and cannot find anything. I am getting embarrased having to keep posting, but in a corner.

 

[jpadie]

no need to be embarassed
do you have a primary key defined on your table? i can't understand why phpmyadmin would be using all these criteria if you have a primary key defined. in the mysql forum the table definition you posted had ID set as an INDEX type index. I would think this really ought to be set as type PRIMARY
use this code to do so (press the sql button and paste the code in)

ALTER TABLE `template` ADD PRIMARY KEY ( `ID` )

also try running the following query to see whether it is a UTF conversion issue.

SELECT *
FROM `template`
WHERE `ID` =37
AND Location = ''
AND OrderNumber = ''
AND Caption = ''
AND OrderDate` IS NULL
AND Group =5
AND Description = 'SX321 LARGE'
AND FujiPartNumber = 'P10WSXL124A'
AND Duration = '124 mins'
AND UnitPrice = 7.94
AND Qty =0
AND OrdSubTotal = 0.00
AND RunningTotal = 0.00;

 

[ZOR]

I am so worried about shouting, but I think you have hit it on the head. I changed my ID to a primary key and I can repeatedly go in and out of all records. It started off as a primary key in Access, but the dumpfile program which I bought, which I relied on doing a proper job messed it up. So MySql must have been searching the world to find the right record. As I say I am scared at the moment to make a lot of noise, but so far from hours of playing, re-installing, re-importing, it seems happy. Well done for spotting it. You deserve a block import of stars.

 

[ZOR]

Think I can shout Thankyou now. It would have crashed by now. Thanks again

 

[jpadie]

glad to hear you're back on the road!