MySql code issue

[banderson407]

Been working on converting an old Module and been chasing my tail trying to convert to new syntax. Any assistance would be appreciated

Original code $query = "SELECT * FROM ".TABLE_PREFIX."vlc_certificate WHERE course_id = $vlcsid" ;

$result = mysql_query($query); $num_rows = mysql_num_rows($result);

if ($num_rows > 0){

$id= '1';
$mbcourse_id = mysql_result($result,0,"course_id");
$mbpscore1 = mysql_result($result,0,"score1");
$mbpscore2 = mysql_result($result,0,"score2");
$mbtaverage = mysql_result($result,0,"taverage");
$mbtotpages = mysql_result($result,0,"totpages");
$mbtotmins = mysql_result($result,0,"totmins");
$mbmaxmins = mysql_result($result,0,"maxmins");
$mbminmins = mysql_result($result,0,"minmins");
$mbbcemail = mysql_result($result,0,"bcemail");

and so on...

I am just trying to return a single row from the table When I use the example for converting to queryDB things seem be going sideways for me when trying to address the TABLE_PREFIX.

[gregrgay]

Here are the details for queryDB() https://github.com/atutor/ATutor/blob/master/include/lib/mysql_connect.inc.php#L110

The mysql functions are deprecated. You should be using mysqli, or better stick to using queryDB() which includes all the appropriate filtering etc, to prevent hacking. To get a single row, you'd use the $onerow=TRUE in the queryDB() function.

[banderson407]

Greg,

When I wrote the code over a decade ago it all seemed to make sense but at the moment I am really struggling to make heads or tails of how to query the data base now. The example you give is a function, is this something I need to copy or is it somehow callable from within my routine? I am probably making it more difficult than it is but at the moment I am totally lost.

Brad

[gregrgay]

There are some good examples of how to use queryDB() in the handbook, in the developer section. It was necessary to replace all mysql related queries, php having deprecated it. In the long run, using queryDB() will be easier than using the mysqli functions. It just takes a little getting used to: https://s2.demo.opensourcecms.com/atutor/documentation/developer/guidelines.html#querydb

[banderson407]

Greg,

Thanks, there are some good examples in there. I've had a few aha moments that were closely followed by brick walls but will keep going through them and testing. Yup, it is taking a lot to get use to the conversion and my head hurts. I'm sure I will have many more question, thanks for the willingness to help. I've have a few weeks to get it converted so we shall see. Thanks again Brad

[banderson407]

Greg,

I have a dumb question (probably). In the following example:

$query = "UPDATE %svlc_certificate SET score1=%d, score2=%d, taverage=%d, bcemail='%s', text=%d, text2=%d WHERE course_id=%d";

$cert_updated = queryDB($query, array(TABLE_PREFIX, $mbscore1, $mbscore2, $mbtaverage,$mbbcenail, $mbtext, $mbtext2, $mbcourse_id));

What are the %d references not in single quotes? (score1='%d') and only the %s is in single quotes. Is it because it references string and not integer?

[gregrgay]

You got it. %d is a digit %s is a string. Only string values use quotes

[banderson407]

Greg,

Thanks, makes sense. Think I am starting to adjust to the queryDB and have about half the calls re-written but hitting a block wall on using a form. I worked the upgrades. Here is the top part of the form:

the routine 'vlccertupdate.php' exists and contains the UPDATE & INSERT queryDB calls but when I submit the form I get a 'page cannot be found' error. Can you think of why or how I can identify where it is going off the tracks? Thanks Brad

[gregrgay]

Hard to say. I'd be checking the form's action value to be sure the path is being constructed correctly. Could not see a screenshot, if that's what you were intending.

[gregrgay]

...and also make sure all the file paths are defined in the module.php file. Have a look at the hello_world module if you need examples.

[banderson407]

I think I changed all the references in the module.php from the yellow_world module The instructor section looks like this:

• instructor Manage section: */ $this->_pages['mods/vlc_certificate/index_instructor.php']['title_var'] = 'vlc_certificate'; $this->_pages['mods/vlc_certificate/index_instructor.php']['parent'] = 'tools/index.php'; // possible alternative: // $this->pages['./index_instructor.php']['title_var'] = 'vlc_certificate'; // $this->pages['./index_instructor.php']['parent'] = 'tools/index.php';

Is there an easy way to trap variables to check.

The form gather the proper information

but when I submit I get:

Not sure what is going on

Brad

[gregrgay]

I don't see an issue with your code snippet. You can output variable values using the debug() function, enabled at the top of the include/vitals.inc.php file.

https://s2.demo.opensourcecms.com/atutor/documentation/developer/guidelines.html#fn-debug

[banderson407]

Greg,

I renamed the routine on the server to zzvlccertuptade.php so the form submit wouldn't;t find it and looked at the address bar at the top of the browser and the path incorrect. so I guess it must be an issue with the routine...??

The routine is pretty small here it is. Maybe something will jump out at you. I have stared at it so long I could be looking right at an issue and not notice it at this point.

<?php define('AT_INCLUDE_PATH', '../../include/'); require (AT_INCLUDE_PATH.'vitals.inc.php'); authenticate(AT_PRIV_VLC_CERTIFICATE); require (AT_INCLUDE_PATH.'header.inc.php'); ?>

// Gather the passed $POST variables

$mbid = $_POST['ud_id']; $mbcourse_id = $_POST['course_id']; $mbscore1 = $_POST['score1']; $mbscore2 = $_POST['score2']; $mbtaverage = $_POST['taverage']; $mbtotpages = $_POST['totpages']; $mbtotmins = $_POST['totmins']; $mbmaxmins = $_POST['maxmins']; $mbminmins = $_POST['minmins']; $mbbcemail = $_POST['bcemail']; $mbtext = $_POST['text']; $mbtext2 = $_POST['text2']; $mbtext3 = $_POST['text3']; $mbcpass = $_POST['cpass']; $mbcfail = $_POST['cfail']; $mbcongrats = $_POST['congrats']; $mbcertprint = $_POST['certprint']; $mbbctext = $_POST['bctext'];

// insert data into database

if ($mbid=1){

$query = "UPDATE %svlc_certificate SET score1=%d, score2=%d, taverage=%d, totpages=%d, totmins=%d, maxmins=%d, minmins=%d, bcemail='%s', text=%d, text2%=%d, text3=%d, cpass=%d, cfail=%d, congrats=%d, certprint=%d, bctext=%d WHERE course_id=%d";

$cert_updated = queryDB($query, array(TABLE_PREFIX, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbminmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbctext, $mbcourse_id));

}

if ($mbid=0){

//add defaults as new data entry $query = "INSERT INTO %svlc_certificate (course_id, score1, score2, taverage, totpages, totmins, maxmins, minmins, bcemail, text, text2, text3, cpass, cfail, congrats, certprint, bctext) VALUES (%d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d, %d, %d, %d, %d )";

$cert_inserted = queryDB($query, array(TABLE_PREFIX, $mbcourse_id, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbbctext));

}

require (AT_INCLUDE_PATH.'footer.inc.php');

?>

Thanks Brad

[gregrgay]

"text2%=%d" looks suspicious in the first query. Extra % sign perhaps.

[banderson407]

You've got better eyes than I do for sure. Yeah that was a typo, I changed it. I am not that good at php so are all the <php , <? , ?> okay?

Now when I submit it just hangs so I think it's headed in the right direction.

Here is the current code, do you see any PhP references that I have messed up

<?php define('AT_INCLUDE_PATH', '../../include/'); require (AT_INCLUDE_PATH.'vitals.inc.php'); authenticate(AT_PRIV_VLC_CERTIFICATE); require (AT_INCLUDE_PATH.'header.inc.php'); ?>

<? // Gather the passed $POST variables

$mbid = $_POST['ud_id']; $mbcourse_id = $_POST['course_id']; $mbscore1 = $_POST['score1']; $mbscore2 = $_POST['score2']; $mbtaverage = $_POST['taverage']; $mbtotpages = $_POST['totpages']; $mbtotmins = $_POST['totmins']; $mbmaxmins = $_POST['maxmins']; $mbminmins = $_POST['minmins']; $mbbcemail = $_POST['bcemail']; $mbtext = $_POST['text']; $mbtext2 = $_POST['text2']; $mbtext3 = $_POST['text3']; $mbcpass = $_POST['cpass']; $mbcfail = $_POST['cfail']; $mbcongrats = $_POST['congrats']; $mbcertprint = $_POST['certprint']; $mbbctext = $_POST['bctext'];

// insert data into database

if ($mbid=1){

$query = "UPDATE %svlc_certificate SET score1=%d, score2=%d, taverage=%d, totpages=%d, totmins=%d, maxmins=%d, minmins=%d, bcemail='%s', text=%d, text2=%d, text3=%d, cpass=%d, cfail=%d, congrats=%d, certprint=%d, bctext=%d WHERE course_id=%d";

$cert_updated = queryDB($query, array(TABLE_PREFIX, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbminmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbctext, $mbcourse_id));

}

if ($mbid=0){

//add defaults as new data entry $query = "INSERT INTO %svlc_certificate (course_id, score1, score2, taverage, totpages, totmins, maxmins, minmins, bcemail, text, text2, text3, cpass, cfail, congrats, certprint, bctext) VALUES (%d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d, %d, %d, %d, %d )";

$cert_inserted = queryDB($query, array(TABLE_PREFIX, $mbcourse_id, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbbctext));

}

<?php require (AT_INCLUDE_PATH.'footer.inc.php');?>

Thanks so much Brad

[banderson407]

<?php define('AT_INCLUDE_PATH', '../../include/'); require (AT_INCLUDE_PATH.'vitals.inc.php'); authenticate(AT_PRIV_VLC_CERTIFICATE); require (AT_INCLUDE_PATH.'header.inc.php'); ?>

<? // Gather the passed $POST variables

$mbid = $_POST['ud_id']; $mbcourse_id = $_POST['course_id']; $mbscore1 = $_POST['score1']; $mbscore2 = $_POST['score2']; $mbtaverage = $_POST['taverage']; $mbtotpages = $_POST['totpages']; $mbtotmins = $_POST['totmins']; $mbmaxmins = $_POST['maxmins']; $mbminmins = $_POST['minmins']; $mbbcemail = $_POST['bcemail']; $mbtext = $_POST['text']; $mbtext2 = $_POST['text2']; $mbtext3 = $_POST['text3']; $mbcpass = $_POST['cpass']; $mbcfail = $_POST['cfail']; $mbcongrats = $_POST['congrats']; $mbcertprint = $_POST['certprint']; $mbbctext = $_POST['bctext'];

// insert data into database

if ($mbid=1){

$query = "UPDATE %svlc_certificate SET score1=%d, score2=%d, taverage=%d, totpages=%d, totmins=%d, maxmins=%d, minmins=%d, bcemail='%s', text=%d, text2=%d, text3=%d, cpass=%d, cfail=%d, congrats=%d, certprint=%d, bctext=%d WHERE course_id=%d";

$cert_updated = queryDB($query, array(TABLE_PREFIX, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbminmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbctext, $mbcourse_id));

}

if ($mbid=0){

//add defaults as new data entry $query = "INSERT INTO %svlc_certificate (course_id, score1, score2, taverage, totpages, totmins, maxmins, minmins, bcemail, text, text2, text3, cpass, cfail, congrats, certprint, bctext) VALUES (%d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d, %d, %d, %d, %d )";

$cert_inserted = queryDB($query, array(TABLE_PREFIX, $mbcourse_id, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbbctext));

}

<?php require (AT_INCLUDE_PATH.'footer.inc.php');?>

[banderson407]

<?php define('AT_INCLUDE_PATH', '../../include/'); require (AT_INCLUDE_PATH.'vitals.inc.php'); authenticate(AT_PRIV_VLC_CERTIFICATE); require (AT_INCLUDE_PATH.'header.inc.php'); ?>

<? // Gather the passed $POST variables

$mbid = $_POST['ud_id']; $mbcourse_id = $_POST['course_id']; $mbscore1 = $_POST['score1']; $mbscore2 = $_POST['score2']; $mbtaverage = $_POST['taverage']; $mbtotpages = $_POST['totpages']; $mbtotmins = $_POST['totmins']; $mbmaxmins = $_POST['maxmins']; $mbminmins = $_POST['minmins']; $mbbcemail = $_POST['bcemail']; $mbtext = $_POST['text']; $mbtext2 = $_POST['text2']; $mbtext3 = $_POST['text3']; $mbcpass = $_POST['cpass']; $mbcfail = $_POST['cfail']; $mbcongrats = $_POST['congrats']; $mbcertprint = $_POST['certprint']; $mbbctext = $_POST['bctext'];

// insert data into database

if ($mbid=1){

$query = "UPDATE %svlc_certificate SET score1=%d, score2=%d, taverage=%d, totpages=%d, totmins=%d, maxmins=%d, minmins=%d, bcemail='%s', text=%d, text2=%d, text3=%d, cpass=%d, cfail=%d, congrats=%d, certprint=%d, bctext=%d WHERE course_id=%d";

$cert_updated = queryDB($query, array(TABLE_PREFIX, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbminmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbctext, $mbcourse_id));

}

if ($mbid=0){

//add defaults as new data entry $query = "INSERT INTO %svlc_certificate (course_id, score1, score2, taverage, totpages, totmins, maxmins, minmins, bcemail, text, text2, text3, cpass, cfail, congrats, certprint, bctext) VALUES (%d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d, %d, %d, %d, %d )";

$cert_inserted = queryDB($query, array(TABLE_PREFIX, $mbcourse_id, $mbscore1, $mbscore2, $mbtaverage, $mbtotpages, $mbtotmins, $mbmaxmins, $mbbcemail, $mbtext, $mbtext2, $mbtext3, $mbcpass, $mbcfail, $mbcongrats, $mbcertprint, $mbbbctext));

}

<?php require (AT_INCLUDE_PATH.'footer.inc.php');?>

[gregrgay]

I suggest making good use of debug() to figure out what variables are outputting, and monitoring the tail of the PHP error log.

[banderson407]

Thanks Greg. I broke out the queryDB call and logic and tested it and it was all good. That was my main concern. That would leave the $_POST logic at the top the routine which along with the 'form' logic from the calling routine has not changed since before.....

Since I have been trying to use the new 'hello world' framework, I may have some issues there.

So at the moment I'm just going to finish converting the remaining mysql_query() and then re-group to see if I can roll it back to the original module framework or try to get a lot smarter on the new one. Coming from a M, Cache database world it's a bit of a learning curve. Even though I wrote this module about 15 years ago, it is like starting again with a blank slate....where the slate is my brain.

Thanks for helping, I sure I'll be back again with questions.

Brad

[banderson407]

Greg,

Another question in regards to queryDB format:

I can follow the use of %d & %s when they are replacing vaiarbles (number vs string) but what happens when it's just a field reference?

For example (original query):

$query = "SELECT * FROM ".TABLE_PREFIX."content WHERE content_id = $certificate ORDER BY course_id";
$result = mysql_query($query);

Would it become:

$query = "SELECT * FROM %scontent WHERE content_id = %d ORDER BY course_id";
$rows = queryDB($query, array(TABLE_PREFIX, $certificate)

Or does the something need to be done with the course_id even though is it just a sort reference?

Hope that makes sense Thanks Brad

[gregrgay]

Not sure what the filters do to references. I'd suggest experimenting.

[banderson407]

Well, I've tried everything and can't get the ORDER filter to work for sorting the returned array. I can probably use 'usort' after the fact but am sure it will cause some performance issues. The latest stumbling block at the moment is getting the test results for a student. When I wrote it before I think I had to jump from the test_results file to the test file to filter by course_id (I think). I think it had something to do with survey or poll data being in there as well. I'm sure I will need to use an 'INNER JOIN' or a 'LEFT/RIGHT JOIN' to get there some how but have no clue as far as the syntax. Here is the original query: $query = "SELECT * FROM ".TABLE_PREFIX."tests_results WHERE (member_id = $vlcmbid) AND (test_id IN(SELECT test_id FROM ".TABLE_PREFIX."tests WHERE course_id = $vlcsid)) ORDER BY test_id"; $result = mysql_query($query);

Do you know of a better way to get the test result data for the students? Maybe there is a function already in Atutor. Or can you point me to someone (you included) that has the experience and willing to help. Not looking for a free ride, willing to pay. Just running out of time.

Thanks Brad

[gregrgay]

It's been a few years since I've even looked at ATutor code, and I don't have a dev environment setup anymore to experiment with. It would take time, which I don't really have for ATutor anymore.

As for PHP developers that could help, I don't know of any currently working with ATutor (its been unsupported for a couple years now).

Sorry I can't be of more help. Life has changed, and I've moved on.

[banderson407]

I can certainly understand that. I've been sort of retired for a while my self and this is the first time I've needed to work on anything related to ATutor. It's a shame it is no longer supported, suppose I will have to migrate to something else before another mysql/php upgrade grinds it to a complete halt. Should you change your mind, I have a development environment (or two)available. Thanks so the help you've given Brad

[gregrgay]

Thanks for understanding.

Hopefully there won't be another big change, like eliminating mysql functions, for a while, and ATutor will continue to work for some time yet. But eventually yes, bugs are likely begin to popup as PHP evolves, but that should be a few years off.

My hope is, an organization will pick up on ATutor where I left off, and it will continue to live on.

Fingers crossed.

Good luck in your effort.

[banderson407]

Yeah, that would be great if some organization picked it up. It's an awesome program/system and I know you put a ton of time into it. I know how that goes, have 30+ years developing hospital systems for the government. Back when out government wasn't shit that is. I'll keep plugging away on this, it's the last query I have to convert.....unfortunately it is the most important one....of course. Enjoy the semi-retirement. Brad

[banderson407]

Greg,

Everything got sorted out. Upgrade done and module installed. The issue I am having at the moment is that when taking a test, the test score is not showing on the test/summary page (see attached) it is showing NA but the score is stored in the tests_results file. Any idea where to look to correct this? Brad

[gregrgay]

Tests with 1 attempt are marked.

[gregrgay]

Resolve this thread when you're done. Start a new one for new questions.

[banderson407]

Moving to new thread