[RESOLVED] mssql to mysql

fritobag · May 18, 2006

We moved our website to a new server and converted our database from mssql to mysql. Now, the resource_dir.php is working, but the next level, resource_cat.php is not working. You can see the pages at:

http://www.birf.info/home/directory/resource_dir.php/
http://www.birf.info/home/directory/resource_cat.php/

The latter page shows the following error:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /hsphere/local/home/fritobag/birf.info/home/directory/resource_cat.php on line 486

Here's a portion of the code. The error, line 486, is the second "while" loop. We'd really appreciate any advice/help we can get. Thanks.

<?php
$db = mysql_connect("erased", "erased", "erased");
mysql_select_db("fritoba_forms",$db);
$titlequery = "select CategoryName from fritoba_forms.Category where CategoryId = XXXX;";
$id = $_REQUEST['cat_id'] ;
$titlequery = str_replace("XXXX", $id, $titlequery);
$query = "select * from fritoba_forms.Provider where fritoba_forms.Provider.ProviderId in (select ProviderId from fritoba_forms.Junction where fritoba_forms.Junction.CategoryId = XXXX);" ;
$query = str_replace("XXXX", $id, $query);
$result = mysql_query($query,$db);
$titleresult = mysql_query($titlequery,$db);
echo "<table border=0>\n";
while ($mytitlerow = mysql_fetch_row($titleresult)) {
printf("<tr><td><font class=\"arthead\">%s</font></td></tr>", $mytitlerow[0]);
echo "<hr width=\"100%\">";
}
while ($myrow = mysql_fetch_row($result)) {
echo "<tr><td><table border=0>";
printf("<tr><td align=\"right\" class=\"fieldlabel\">Company Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[1]);
printf("<tr><td align=\"right\" class=\"fieldlabel\">Contact Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[2]);
if($myrow[3]) {
printf("<tr><td align=\"right\" class=\"fieldlabel\">Address:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[3]);
} ...........

jheitz · May 18, 2006

Hi,

Can't see anything obvious (although your str_replace statements seem a bit of an overhead and are not really needed, but not a great deal). As a quick test try an "echo $query" just before the while that produces the error and run that SELECT statement in MySQL to see if it fails and if so, why... It might not help, but again it might

bpat1434 · May 19, 2006

Hey... you dont' define what table to look in!!

select CategoryName from fritoba_forms.Category where CategoryId = XXXX;

SQL syntax is:
mySQL Select Syntax (13.2.7):

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FOR UPDATE | LOCK IN SHARE MODE]]

So yours probably should be written:

SELECT CategoryName FROM Category WHERE CategoryID='XXXX'

But I don't know what your table names are....

jheitz · May 19, 2006

I assumed his database was called: fritoba_forms. If his table is called Category then his syntaxis is Ok (MySQL allows you to reference a table as "databasename.tablename")

bpat1434 · May 19, 2006

Okay... then perhaps echoing out mysql_error() and mysql_errno() can help getting us going in the right direction.

I wasn't aware you could reference it like database_name.table_name.column_name like in the second query.

jheitz · May 19, 2006

Me neither to be honest. But I was curious as if that would be an error in MySQL as it is allowed in SQL Server. As I have both RDBMSs installed on my PC I quickly confirmed that is allowed

Weedpacket · May 19, 2006

It oughn't be necessary in this case anyway, though, since the database has already been selected.

I don't see offhand what could be causing the problem either (when I looked, both loops were failing). I do wonder about why the XXXX and str_replace parts, and I do see the opportunity for SQL injection .... ah, that could be something: I'm guessing from the URL that there's some rewriting going on - that hasn't gone wonky for some reason, leaving $_REQUEST['cat_id'] with something bogus has it? As bpat1434 suggests, a bit of debugging with mysql_error ought to turn something up in that regard.

fritobag · May 19, 2006

I meant to post my thread in the newby section, so you'll have to excuse my ignorance. I'd like to try your advice, but I'm not sure how. I gather the concensus is I should try echoing out mysql_error() and mysql_errno() to help determine the right direction. I'm not really sure how to do this. I'll look through my resources and give a few tries.

fritobag · May 19, 2006

I don't know if I did what was advised, but I put "echo $query" before the while loop causing the problem. The new error message reads: "Parse error: parse error, unexpected T_WHILE, expecting ',' or ';' in /hsphere/local/home/fritobag/birf.info/home/directory/resource_cat.php on line 487"

fritobag · May 19, 2006

Okay... I forgot to put a ";" after "echo $query". That done, this is the error message:

"select * from fritoba_forms.Provider where fritoba_forms.Provider.ProviderId in (select ProviderId from fritoba_forms.Junction where fritoba_forms.Junction.CategoryId = 5);
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /hsphere/local/home/fritobag/birf.info/home/directory/resource_cat.php on line 487"

Weedpacket · May 20, 2006

Well, assuming you're using a fairly current version of MySQL (and why wouldn't you install the latest stable?) then the query looks right. So let's check that you're actually connecting.

Alter some lines as shown:

$db = mysql_connect("erased", "erased", "erased") or die("Connecting to DBMS: ".mysql_error());
mysql_select_db("fritoba_forms",$db) or die("Selecting database fritoba_forms: ".mysql_error());

$result = mysql_query($query,$db) or die("Using query: $query.\n".mysql_error());
$titleresult = mysql_query($titlequery,$db) or die("Using query: $query.\n".mysql_error());

fritobag · May 20, 2006

I'll give it a try but I believe I'm connecting to the database because the following link gains access:

http://www.birf.info/home/directory/resource_dir.php/

As well, http://www.birf.info/home/directory/resource_cat.php/ shows a connection by posting the category title. It fails after that point.

I'm wondering if something is a miss with the SQL? I'd love for someone to look at the tables to see if there's a problem on that end. Do think that would be possible... if I emailed you access info??

FYI: Our old server still posts the MSSQL database, which you can view at:

http://209.35.178.249/home/directory/resource_cat.php?cat_id=3

fritobag · May 20, 2006

The error message read:

Using query: select * from Provider where Provider.ProviderId in (select ProviderId from Junction where Junction.CategoryId = 24);. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ProviderId from Junction where Junction.CategoryId = 24)

Our hosting company uses MySQL 4.0.26. I'll see if I can find what I need in the manual, but I am a newbe. If you know what it the correct syntax migt be, let me know.

Thanks for everything.

fritobag · May 20, 2006

I think this query is a join. It draws from three tables, one being "Junction," which has three columns, JunctionID, ProviderId, and CategoryID. The function I'm looking for is to post all the providers of the selected category.

I checked out the mysql manual for joins at

http://dev.mysql.com/doc/refman/4.1/en/join.html ?

but it did not help me.

Can anyone figure out the proper syntax for this line?

fritobag · May 22, 2006

Yay! Solved by Bogu...

<?php
$db = mysql_connect("erased", "erased", "erased");
mysql_select_db("fritoba_forms",$db);
$titlequery = "select CategoryName from fritoba_forms.Category where CategoryId = XXXX;";
$id = $_REQUEST['cat_id'] ;
$titlequery = str_replace("XXXX", $id, $titlequery);
$query = "SELECT Provider.* FROM Provider
LEFT JOIN Junction USING(ProviderId)
WHERE Junction.CategoryId = XXXX";
$query = str_replace("XXXX", $id, $query);
$result = mysql_query($query,$db);
$titleresult = mysql_query($titlequery,$db);
echo "<table border=0>\n";
while ($mytitlerow = mysql_fetch_row($titleresult)) {
printf("<tr><td><font class=\"arthead\">%s</font></td></tr>", $mytitlerow[0]);
echo "<hr width=\"100%\">";
}
while ($myrow = mysql_fetch_row($result)) {
echo "<tr><td><table border=0>";
printf("<tr><td align=\"right\" class=\"fieldlabel\">Company Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[1]);
printf("<tr><td align=\"right\" class=\"fieldlabel\">Contact Name:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[2]);
if($myrow[3]) {
printf("<tr><td align=\"right\" class=\"fieldlabel\">Address:</td><td class=\"arttext\">%s</td></tr>\n", $myrow[3]);
} ...........

bpat1434 · May 23, 2006

This:

$query = "SELECT Provider.* FROM Provider
LEFT JOIN Junction USING(ProviderId)
WHERE Junction.CategoryId = XXXX";
$query = str_replace("XXXX", $id, $query);

can be rewritten as:

$query = sprintf("
    SELECT Provider.* 
    FROM Provider 
    LEFT JOIN Junction 
        USING (ProviderId) 
    WHERE Junction.CategoryId='%d'", 
    $id);

Can make for nicer queries, and more readable code later on

And there's a way to combine both queries so that you only have to run one query with 2 joins, and then your loop would become slightly more complicated, but not much more.

[RESOLVED] mssql to mysql

Ffritobag

Jjheitz

bpat1434

Jjheitz

bpat1434

Jjheitz

Weedpacket

Ffritobag

Ffritobag

Ffritobag

Weedpacket

Ffritobag

Ffritobag

Ffritobag

Ffritobag

bpat1434