cyan studios Posted January 19, 2009 Share Posted January 19, 2009 Is this possible? Basically, I'm getting values from an ODBC (SQL) connection and these will echo into a written XML structure. So I have one element called <valid> and another called <valcatch>. I need one row to feed the values for "valid", and two others to feed into "valcatch", but that means that somewhere during the while loop, I need to insert an ending tag for </valid> and a beginning tag for <valcatch>. Here's a snippet to show what I've done. $fname=odbc_result($rs,'fname'); $lname=odbc_result($rs,'lname'); $empl=odbc_result($rs,'empl'); echo '<valid>'; while (odbc_fetch_row($rs)) { echo $empl . ','; } echo '</valid><valcatch>'; while (odbc_fetch_row($rs)) { echo $fname . ' ' . $lname . ','; } echo '</valcatch>'; odbc_close($conn); Am I pursuing the right method? Currently, all of the values for "valid" show up (empl), but after that, I believe the while is already defined and it's not allowing me to allow any further operations during the while loop. Quote Link to comment Share on other sites More sharing options...
elgoog Posted January 19, 2009 Share Posted January 19, 2009 try sticking mysql_data_seek($rs,0); in between $fname=odbc_result($rs,'fname'); $lname=odbc_result($rs,'lname'); $empl=odbc_result($rs,'empl'); echo '<valid>'; while (odbc_fetch_row($rs)) { echo $empl . ','; } echo '</valid><valcatch>'; mysql_data_seek($rs,0); while (odbc_fetch_row($rs)) { echo $fname . ' ' . $lname . ','; } echo '</valcatch>'; odbc_close($conn); If there is only one record though, you may want to do something like: if (odbc_fetch_row($rs)) { $val1 = $empl . ','; $val2 = $fname . ' ' . $lname . ','; } echo $val1; echo '</valid><valcatch>'; echo $val2; echo '</valcatch>'; odbc_close($conn); Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 19, 2009 Author Share Posted January 19, 2009 I'll test this, but it is actually a SQL database, not MySQL, and this is a syntax I've never seen before, otherwise I'd look it up. Still will. What does this do from a logistic standpoint? And thank you very much for your help. Quote Link to comment Share on other sites More sharing options...
elgoog Posted January 19, 2009 Share Posted January 19, 2009 Sorry bout that.... should pay more attention....Not sure if there is similar issue with MSSQL. In mysql when you run a while loop for the second time on the same lot of data, it misses out the first record. something about a pointer moving forward one in the first loop. So if there is only one record there would be no data after the second loop. Try the second method. Probably easier Quote Link to comment Share on other sites More sharing options...
chronister Posted January 20, 2009 Share Posted January 20, 2009 To the best of my knowledge, there is no way to interrupt the loop and then re-start it. It looks like your only dealing with 3 fields. I would loop through them and put them in an array so you have 3 arrays with each key corresponding to the other arrays. e.g. $firstName[0] goes with $lastName[0] & $employee[0] (guessing here). Thats what I would look at. You need to put them in a form where you can correlate each piece with the next. Then you will be able to loop through each item and create your XML the way you need to. I am assuming that there are the same number of items for each. Meaning, the first record in $fname relates to the first records in $lname & $empl. So create arrays out of them, and then array key 0 will correlate for each item and a while loop and a counter var will give you what you want. I don't know how odbc works, so am not sure on how to code it to get each row and such. But I hope this makes sense. Nate Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 20, 2009 Author Share Posted January 20, 2009 I tried that out and the end result was oddly returning "Array" just once in each field, but that may have something to do with the PDF (XFA) API. Array really did seem like the obvious way to go here, so I'll give elgoog's if statement a whirl, otherwise, I'll see if I can brute force some sort of array into it. I do appreciate the help guys. Quote Link to comment Share on other sites More sharing options...
btherl Posted January 20, 2009 Share Posted January 20, 2009 "Array" means you tried to echo an array, instead of echoing an item from the array. You can use var_dump() or print_r() to see the structure of the item you want to display. Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 20, 2009 Author Share Posted January 20, 2009 I'm still getting "Array" with print_r. <?php $thePost = $HTTP_RAW_POST_DATA; $conn=odbc_connect('*connection properties*'); if (!$conn) {exit('Connection Failed: ' . $conn);} $sql="SELECT fname, lname, empl FROM PREmployees"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit('Error in SQL');} $fname=array($rs,'fname'); $lname=array($rs,'lname'); $empl=array($rs,'empl'); $xdpStr = '<?xml version="1.0" encoding="UTF-8"?> <?xfa generator="AdobeLiveCycleDesignerES_V8.2.1.3144.1.471865" APIVersion="2.8.8118.0"?> <xdp:xdp xmlns:xdp="http://ns.adobe.com/xdp/"> <xfa:datasets xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/"> <xfa:data> <form1> <p1> <valid>'; header('Content-Type: application/vnd.adobe.xdp+xml'); echo $xdpStr; print_r ($empl . ','); $xdpStr2 = '</valid> <valcatch>'; echo $xdpStr2; print_r ($fname . ' ' . $lname . ','); $xdpStr3 = '</valcatch> </p1> </form1> </xfa:data> </xfa:datasets> <pdf href="http://localhost/parsetest.pdf" xmlns="http://ns.adobe.com/xdp/pdf/"/> </xdp:xdp>'; echo $xdpStr3; odbc_close($conn); ?> That's the entire script I'm working with, which merges into a LiveCycle Designer form. I've considered and tried a few different possibilities, such as odbc_fetch_array, instead of array, etc. I'm looking all over for anything that will show me if my syntax for connecting to the ODBC and fetching the array is right or not, but everything I find seems to be intended for much more complicated scenarios. The IF statement won't work because these values are populating a drop down, hence, I need to cycle through every record in the database and echo the values, followed by commas. Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 20, 2009 Author Share Posted January 20, 2009 Wow, that really botched it. I won't get all of the results if I don't work the $fname etc. variables into a while loop as seen at the bottom of this post. I need an alternative to doing a while loop for all of my variables that can be split up. Like I said before, arrays seem to require a specification toward which index of the record I need, and I need all of them, and the amount of them varies. <?php $thePost = $HTTP_RAW_POST_DATA; $conn=odbc_connect(*connection properties*); if (!$conn) {exit('Connection Failed: ' . $conn);} $sql="SELECT fname, lname, empl FROM PREmployees"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit('Error in SQL');} $xdpStr = '<?xml version="1.0" encoding="UTF-8"?> <?xfa generator="AdobeLiveCycleDesignerES_V8.2.1.3144.1.471865" APIVersion="2.8.8118.0"?> <xdp:xdp xmlns:xdp="http://ns.adobe.com/xdp/"> <xfa:datasets xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/"> <xfa:data> <form1> <p1> <valid>'; header('Content-Type: application/vnd.adobe.xdp+xml'); echo $xdpStr; while (odbc_fetch_row($rs)) { $empl=odbc_result($rs,'empl'); echo $empl . ','; } $xdpStr2 = '</valid> <valcatch>'; echo $xdpStr2; while (odbc_fetch_row($rs)) { $fname=odbc_result($rs,'fname'); $lname=odbc_result($rs,'lname'); echo $fname . ' ' . $lname . ','; } $xdpStr3 = '</valcatch> </p1> </form1> </xfa:data> </xfa:datasets> <pdf href="http://localhost/parsetest.pdf" xmlns="http://ns.adobe.com/xdp/pdf/"/> </xdp:xdp>'; echo $xdpStr3; odbc_close($conn); ?> Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 20, 2009 Author Share Posted January 20, 2009 Sorry for the triple post but I found a sloppy work around and couldn't edit any posts. I'm sure I could improve it with some help. Under $rs=odbc_exec($conn,$sql); I created $result=odbc_exec($conn,$sql); In the second while loop, I replaced $rs with $result and it works fine, but it's really a gimped out way to do it. Open to suggestions. Quote Link to comment Share on other sites More sharing options...
chronister Posted January 20, 2009 Share Posted January 20, 2009 I don't know anything about the odbc functions, so I am not sure how they work. But I would imagine that they are similar to the mysql functions in that you run the query with 1 function, and then have to loop through the results of the query and fetch the data with another. In mysql the solution I suggested, would go something like this... <?php $result = mysql_query("SELECT * FROM table WHERE this='$that'") $x = 0; while($row = mysql_fetch_array($result)) { $field1[$x] = $row['field1']; $field2[$x] = $row['field2']; $field3[$x] = $row['field3']; $x++; } // now we have the results in arrays that have corresponding keys. $x = 0; while($x < count($field1)) { echo $field1[$x] .' - '. $field2[$x] .' - '. $field3[$x]; $x++; } ?> This is the principle of what I suggested. You may have to modify things to suit your data structure, and translate to the odbc functions that relate to the mysql counterparts. That is the only help I can give ya...... For my own knowledge, why is the odbc_exec() part you have a "gimped out" way to do it? What is wrong with it? Nate Quote Link to comment Share on other sites More sharing options...
btherl Posted January 21, 2009 Share Posted January 21, 2009 Like this: print_r ($fname); print ' ';print_r($lname); print ','; By using the "." on $fname, you're actually converting it from an array into the string "Array". PHP's auto conversion can be nice, but sometimes it's a real pain. Regarding your initial problem, you should use an array. If you're not sure how to, it's time to learn They make life so much easier. Basically the array allows you to store all the data in a form where you can access any item of any row easily. Then you can have as many while loops as you want, all going over the same array. If you have very large data sets an array may not be appropriate though, if there's enough that you'll run out of memory. Quote Link to comment Share on other sites More sharing options...
cyan studios Posted January 21, 2009 Author Share Posted January 21, 2009 I got an array working after taking some shots at what would be different between mysql and sql syntax. I'll post what I found out for the people that use Google once I get back to my work machine. Thank you for that info though, I had no idea a "." would cause PHP to do that to my array. That's pretty chintzy. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.